Cant Figure out a good way to query a datatable in vb.net

So I'm making an application that gets all the events for a month out of a mysql DB and adds them to a calendar. I've got the events in a data table atm "dbTable"

The events are ascending by date

"SELECT * FROM table_events WHERE date BETWEEN '" & startDate & "' AND '" & endDate & "' ORDER BY date ASC"

Now I need to query each day one at a time to check for up to 6 events per date, any suggestions? Im not even sure on how to query the data table let alone do it for up to 31 days and make it somewhat efficient.

MySQL not executing (Java)

For some reason I'm not able to update my database using this code. Can someone help me out? What I've got is when a player completes a certain task it records the time it took them if and only if their time is lower than the best recorded time.

    public void setScores(MapleCharacter chr, int mapid, float time) {
    Connection con1 = DatabaseConnection.getConnection();
    try {
        PreparedStatement ps;
        ps = con1.prepareStatement("SELECT time from jumpquests WHERE characterid = ? AND mapid = ?");
        ps.setInt(1, chr.getId());
        ps.setInt(2, chr.getMapId());
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            if (time < rs.getFloat("time")) {
                executeScores(chr, mapid, time);
            }
        } else {
            executeScores(chr, mapid, time);
        }
        rs.close();
        ps.close();
    } catch (Exception Ex) {
        System.out.println("Ran into exception.");
    }
}

public void executeScores(MapleCharacter chr, int mapid, float time) {
    System.out.println("Setting scores for " + chr.getName() + " for map " + mapid + " at time " + time);
    Connection con1 = DatabaseConnection.getConnection();
    try {
        PreparedStatement ps = con1.prepareStatement("REPLACE INTO jumpquests (characterid, mapid, time) values (?,?,?)");
        ps.setInt(1, chr.getId());
        ps.setInt(2, chr.getMapId());
        ps.setFloat(3, time);
        ps.close();
    } catch (Exception e) {
        System.out.println("Executing scores ran into exception.");
    }
}

duplicate data using INNER JOIN issue

I am building a project and want to retrieve data from three different tables so am using INNER JOIN but just worked out it is duplicating the data, below is what it is currently doing

Name: Ian Haney
First Line of Address: 12C Barclays Bank Chambers
Second Line of Address: Broadway North
Town: Pitsea
County: Essex
Postcode: SS13 3AU
Telephone Number: 01268 206297
Mobile Number: 07538 503276
Car Model: Jeep
Car Number Plate: AB10 1AB
Insurance expiry date: 30 July 2015
Name: Ian Haney
First Line of Address: 12C Barclays Bank Chambers
Second Line of Address: Broadway North
Town: Pitsea
County: Essex
Postcode: SS13 3AU
Telephone Number: 01268 206297
Mobile Number: 07538 503276
Car Model: Jeep
Car Number Plate: AB10 1AB
Tax expiry date: 30 June 2015

what I want is the following

Name: Ian Haney
First Line of Address: 12C Barclays Bank Chambers
Second Line of Address: Broadway North
Town: Pitsea
County: Essex
Postcode: SS13 3AU
Telephone Number: 01268 206297
Mobile Number: 07538 503276
Car Model: Jeep
Car Number Plate: AB10 1AB
Tax expiry date: 30 June 2015
Insurance expiry date: 30 July 2015
MOT expiry date: 30 August 2015

is that possible to do?

my coding is below

<?php

if (logged_in() == false) {
    redirect_to("login.php");
} else {
    if (isset($_GET['id']) && $_GET['id'] != "") {
        $id = $_GET['id'];
    } else {
        $id = $_SESSION['user_id'];
    }

    ## connect mysql server
        $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        # check connection
        if ($mysqli->connect_errno) {
            echo "<p>MySQL error no {$mysqli->connect_errno} : {$mysqli->connect_error}</p>";
            exit();
        }

    ## connect mysql server
        $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        # check connection
        if ($mysqli->connect_errno) {
            echo "<p>MySQL error no {$mysqli->connect_errno} : {$mysqli->connect_error}</p>";
            exit();
        }
    ## query database
        # fetch data from mysql database

        $sql = "SELECT v.visitor_id, visitor_name, visitor_email, visitor_firstline, visitor_secondline, visitor_town, visitor_county, visitor_postcode, visitor_tel, visitor_mobile, visitor_model, visitor_plate, item.description, renewal_id, DATE_FORMAT(renewal_date, '%e %M %Y') as datedue, renewal_date FROM visitors v 
        INNER JOIN renewal USING (visitor_id)
        INNER JOIN item USING (item_id)
        WHERE renewal_date >NOW()";


        if ($result = $mysqli->query($sql)) {
            $user = $result->fetch_array();
            } else {
            echo "<p>MySQL error no {$mysqli->errno} : {$mysqli->error}</p>";
            exit();
            }

    if(mysqli_num_rows($result)) {  

   //fetch the data from the database 
while ($row = mysqli_fetch_array($result)) {

# echo the user profile data
            /*echo "<p>User ID: {$user['id']}</p>";*/
            echo "<p>Name: {$user['visitor_name']}</p>";
            echo "<p>First Line of Address: {$user['visitor_firstline']}</p>";
            echo "<p>Second Line of Address: {$user['visitor_secondline']}</p>";
            echo "<p>Town: {$user['visitor_town']}</p>";
            echo "<p>County: {$user['visitor_county']}</p>";
            echo "<p>Postcode: {$user['visitor_postcode']}</p>";
            echo "<p>Telephone Number: {$user['visitor_tel']}</p>";
            echo "<p>Mobile Number: {$user['visitor_mobile']}</p>";
            echo "<p>Car Model: {$user['visitor_model']}</p>";
            echo "<p>Car Number Plate: {$user['visitor_plate']}</p>";       
            echo "<p>" . $row['description'] . " expiry date: " . $row['datedue'] . "</p>\n";

}
        } else { // 0 = invalid user id
            echo "<p><b>Error:</b> Invalid user ID.</p>";
        }

}

?>

Select Last Distinct Value and TIMEDIFF Based on Selection?

I'm trying to setup a query that does a DATEDIFF between two times, based on when the last unique value in one column is present. The data is structured as follows:

  row   ticket_id   create_time      change_time    owner_id     queue_id
 1         11234    5/12/2014 13:47 5/12/2014 13:47        2        4
 2         11234    5/12/2014 13:47 5/12/2014 13:47        2        4
 3         11234    5/12/2014 13:47 5/12/2014 13:47        8        11
 4         11234    5/12/2014 13:47 5/12/2014 13:47        8        11
 5         11234    5/12/2014 14:02 5/12/2014 14:02        3        9
 6         11234    5/12/2014 14:10 5/12/2014 14:10       17        5
 7         11234    5/14/2014 12:00 5/14/2014 12:00       17        5
 8         11234    5/15/2014 12:27 5/15/2014 12:27       17        5

Basically, I want to do a datediff between rows 6 and 8 for the "change_time" column. I want to select the final distinct number in either the owner_id column or queue_id column for each ticket_id and calculate the difference in change times. Is there a way this could be setup using MySQL? Using a MAX() function won't work unfortunately because highest and second highest change times are not always associated with the final queue id or owner id. I know in SAS a similar operation can be performed using a combination of do loops and counter+1, but is something like this possible with SQL?

Encrypt Amazon RDS

I want to create a RDS with MySQL on it, and I want it to be encrypted.

I am using the Ruby API, and I've looked into the RDS client API, and I saw that there are params that can be given:

tde_credential_arn
tde_credential_password

but both are related to oracle DB (Encrypting Amazon RDS Resources). I've also tried to use key storage_encryped and give it a true value, but the key wasn't a valid one (also I've seen it here: CreateDBInstance).

So, how can i do it with MySQL RDS ?

Where to find GoDaddy hostname for MySQL server?

the GoDaddy website updated a while back and now you can't view your hostname though the "details" button because the button no longer exists. I'm trying to set up a MySQL server on GoDaddy but I can't find the hostname of the server which I need to use mysql_connect().

Does anyone know where to find this information now? Thanks.

how to store json in database

Is this better

{"details":{"number":"8","date":"29/06/2015","due_date":"06/07/2015"},

or this one:

%7B%22details%22%3A%7B%22number%22%3A%228%22%2C%22date%22%3A%2229%2F06%2F2015%22%2C%22due_date%22%3A%2206%2F07%2F2015%22

to store in a database?

Thank you.

error when writing variable to mysql via python

I am trying to insert with python (v 2.7.6) a variable with multiple entries into mysql (Ver 14.14 Distrib 5.5.43) table. The code is as follows :

cur = con.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS Stations(     \
            StationsID  INT AUTO_INCREMENT,           \
            Code      VARCHAR(3)       ,              \
            PRIMARY KEY pk_Stations (StationsID)      \
            );");

cur.executemany("INSERT INTO Stations (Code) VALUES(?)", sns);

sns variable has the following form:array(['PAL', 'TT1', 'BAL', 'MHD', 'BI5', 'CB4'],dtype='|S3')

I am getting the following error: File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 199, in executemany if not args: return ValueError: The truth value of an array with more than one element is ambiguous. Use a.any() or a.all()

Could you please give me a help here?

can`t install flask-mysql fedora

i`m trying to install flask-mysql with pip on fedora 22, but I get this error:

Collecting flask-mysql
  Using cached Flask_MySQL-1.3-py2.py3-none-any.whl
Requirement already satisfied (use --upgrade to upgrade): Flask in /usr/lib/python2.7/site-packages (from flask-mysql)
Collecting MySQL-python (from flask-mysql)
  Using cached MySQL-python-1.2.5.zip
    Complete output from command python setup.py egg_info:
    sh: mysql_config: command not found
    Traceback (most recent call last):
      File "<string>", line 20, in <module>
      File "/tmp/pip-build-IH7lNv/MySQL-python/setup.py", line 17, in <module>
        metadata, options = get_config()
      File "setup_posix.py", line 43, in get_config
        libs = mysql_config("libs_r")
      File "setup_posix.py", line 25, in mysql_config
        raise EnvironmentError("%s not found" % (mysql_config.path,))
    EnvironmentError: mysql_config not found

    ----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-IH7lNv/MySQL-python

I searched a lot, but all the solutions were for debian based os`s which needed to download libmysqlclient-dev but there is no such package in fedora

Password reset PHP

I am new to website design and I have recently made a website and i would like to add a reset password function, it doesn't work.

The SQL connection is inside of the init.php file

<?php
include('core/init.php');
include('includes/overall/header.php');
echo "
<h1>Reset Password</h1>


<div class='Reset' align='center'>
<form action='forgot_pass.php' method'POST'>
Enter your username<br><input type='text' name='username'><p>
<br>
Enter your email<br><input type='email' name='email'><p>
<input type='submit' value='Submit' name='submit'>
</form>
</div>
";

if (isset($_POST['submit']))
{
$username = $_POST['username'];
$email = $_POST['email'];

$query = mysql_query("SELECT * FROM `users` WHERE `username`='$username'");
$numrow = mysql_num_rows($query);

if ($numrow!=0)
{
    while($row = mysql_fetch_assoc($query))
    {
        $db_email = $row['email'];
    }
    if ($email == $db_email)
    {
        $code = rand(10000,1000000);

        $to = $db_email;
        $subject = "Password Reset";
        $body = "

        Automated email. Click the link
        http://ift.tt/1U1Buug

        ";

        mysql_query("UPDATE users SET passreset='$code' WHERE username='$username'");
        mail($to,$subject,$body);

        echo "Check Email";
    }
    else
    {
        echo "Email not correct";
    }
} else {
    echo "That user does not exist";
    }


}

?>

I will be so happy if somebody could help me thanks

How to become MySQL trainer?

I'm a certified MySQL developer and I have over a decade of SQL experience, most of it MySQL.

What are the steps to take to became a MySQL trainer?

Paypal delay inserting into database

I created a login/register system. After registration users must select payment option clicking on paypal buttons. After payment has been done, users return to my website. Behind all this process, I insert paypal variables into database along with username from my $username = $_SESSION['username'].

Problem is that paypal variables have a considerable delay to be inserted into my database. Then, only username is being inserted. payer_email, item_name, item_number are empty. If I remove username isertion, everything is inserted after a while. Any solution for this?

PHP Optmization: Processing millions of MySQL records?

I've got a handful of databases with, potentially, millions of records that I need to run some backend services on pretty frequently (backups, reporting, etc)

Currently I'm batching my requests in batches of 1k to try and speed things up. It helps a little, but not really enough to notice. Some reports customers want to generate can take a few days - which is preposterous.

Then there's backups. My company has a unique way to store our records and we need to process each and every one individually so I can't just export everything into a file and save it off site.

So, as you can imagine, this causes a lot of backlog pretty quickly.

What are some methods I can look into for speeding this up?

I've already examined using mysqli_poll to help, but I still end up with blocking methods while each batch gets processed.

Is threading with pthreads really my only option to dramatically speed things up at this point or do I need to convince the superiors to use something that's actually threaded to make this actually work.

Thanks in advance for your help!

"IDENTIFIED BY 'password'" in MySQL

I often see in many MySQL tutorials that people use command IDENTIFIED BY 'password' both during user creation and granting him privileges.

For example:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost' IDENTIFIED BY 'password';

I tried using GRANT without IDENTIFIED BY and it works.
Can somebody explain me why it is used twice? Could there be other password for specific privileges?

MySQL group by all columns except one

I'm looking for a (cleaner?) way to do the following:

Let's say I have a table, main, with ~15 columns that looks something like this, with one row per id:

main:
id      start           end             col4    ...     col15
666     2014-01-01      2014-06-30      ...     ...     ...
1234    2015-03-05      2015-05-02      ...     ...     ...
9876    2014-09-01      2015-01-01      ...     ...     ...
...(etc)

Then I have another table, events, which may have 0, 1, or many rows per id:

events:
id      date            code
666     2014-01-20      "code_a"
1234    2015-05-01      "code_b"
666     2014-01-25      "code_c"
666     2014-02-09      "code_z"
... (etc)

and finally I have a table, codes, which has one row per code, giving a description for the code as well as a type (0,1, or 2):

codes:
code            desc            type
"code_a"        "something"     0 
"code_b"        "somethn else"  1
"code_c"        "another thing" 0
"code_d"        "one more"      2
(no code z)

and what I want as a result is main's 15 columns plus three additional columns which contain comma separated lists of event codes which happened between the start and end dates for that id by type (first column is type 0, second type 1, third type 2), so:

id      start           end             ...     col15   type_0          type_1  type_2
666     2014-01-01      2014-06-30      ...     ...     "code_a,code_c"         
1234    2015-03-05      2015-05-02      ...     ...                     "code_b"
...(etc)

my solution is

select m.*
     , group_concat(c0) as type_0
     , group_concat(c1) as type_1
     , group_concat(c2) as type_2
from main m 
     left join events e on m.id = e.id and e.date between m.start and m.end
     left join codes c0 on c0.code = e.code and c0.type = 0
     left join codes c1 on c0.code = e.code and c0.type = 1
     left join codes c2 on c0.code = e.code and c0.type = 2
group by m.id
       , m.start
       , m.end
       , m.col4
       , m.col5
       , m.col6
       , m.col7
       , m.col8
       , m.col9
       , m.col10
       , m.col11
       , m.col12
       , m.col13
       , m.col14
       , m.col15  

But to me that's pretty nasty looking. Is there a more elegant way to do this (especially avoiding the 15 columns listed in the group by)?

(PHP, mysql) Copy column values to another column in the same table

I'm trying to copy title column to keywords column in database, so the keywords will be inserted automatically from the title.

http://ift.tt/1C2Pa2I

I want to add comma ', ' before each word for example.

" It's my first program "   

it will turn into

" It's, my, first, program, "

This the code I wrote.

<?php

  // $id =mysql_insert_id;
  $select_posts = mysql_query("SELECT * FROM `posts`");

  while($row = mysql_fetch_array($select_posts)){
        $id  = $row['post_id'];
        $text =  $row['post_title'];  

       $delim = ' \n\t,.!?:;';
       $tok = strtok($text, $delim);


    while ( $tok !== false){
          echo $tok1 = $tok.',';
          mysql_query("UPDATE `posts` SET  `post_keywords` =  '$tok1' WHERE `post_id` = $id  ");
          $tok = strtok($delim);
        }   
}

?>    

it insert the last word in each title column , because the words is overwritten by while loop.

Please help me .

MS SQL Read value with apostrophe from table and save it in another table

I am reading a value from table with apostrophe with which I create a dynamic query and than I run a sp to save it in another table, which works fine without apostrophe but throw an error when it contains an apostrophe.

e.g. set @sql = 'exec nameOfSP' + @arguments

@arguments value comes from database

Clear MySQL Cache

I took over a project written in Laravel 4. We have MySQL 5.6.21 - PHP 5.4.30 - currently running on Windows 8.1.

Every morning on the first attempt to access the landingpage - which contain about 5 queries on the backend - this site will crash with a php-timeout (over 30 seconds for response).

After using following I got closer to the cause: Laravel 4 - logging SQL queries. One of the queries takes more than 25 seconds on the first call. After that its always < 0.5 seconds.

The query has got 3 joins and 2 subselects wrapped in Cache::remember. I want to go into optimizing this so that on production it won't run into this problem.

So I want to test different SQLs The Problem is that the first time the data gets cached somehow and then I can't see whether my new SQL's are better or not.

Now, since I guess it's a caching issue (on the first attempt it takes long, afterwards not) I did these:

MySQL: FLUSH TABLES;
restart MySQL
restart Apache
php artisan cache:clear

But still, the query works fast. Then after some time I don't access the database at all (can't give an exact time, maybe 4 hours of inactivity) it happens again.

Explain says:

1 | Primary | table1 | ALL | 2 possible keys | NULL | ... | 1010000 | using where; using temporary; using filesort
1 | Primary | table2 | eq_ref | PRIMARY | PRIMARY | ... | 1 | using where; using index
1 | Primary | table3 | eq_ref | PRIMARY | PRIMARY | ... | 1 | using where; using index
1 | Primary | table4 | eq_ref | PRIMARY | PRIMARY | ... | 1 | NULL
3 | Dependent Subquery | table5 | ref | 2 possible keys | table1.id | ... | 17 | using where
2 | Dependent Subquery | table5 | ref | 2 possible keys | table1.id | ... | 17 | using where

So here the questions:

• What's the reason for this long time?
• How can I reproduce it? and
• Is there a way to fix it?

I read mysql slow on first query, then fast for related queries. However that doesn't answer my question on how to reproduce this behaviour.

---

Update

I changed the SQL and now it is written like:

select 
    count(ec.id) as asdasda

from table1 ec force index for join (PRIMARY)
    left join table2 e force index for join (PRIMARY) on ec.id = e.id
    left join table3 v force index for join (PRIMARY) on e.id = v.id 

where
    v.col1 = 'aaa'
    and v.col2 = 'bbb'
    and v.col3 = 'ccc'
    and e.datecol > curdate()
    and e.col1 != 0

Now explain says:

+----+-------------+--------+--------+---------------+--------------+---------+-----------------+--------+-------------+
| id | select_type | table  | type   | possible_keys | key          | key_len | ref             | rows   | Extra       |
+----+-------------+--------+--------+---------------+--------------+---------+-----------------+--------+-------------+
|  1 | SIMPLE      | table3 | ALL    | PRIMARY       | NULL         | NULL    | NULL            | 114032 | Using where |
|  1 | SIMPLE      | table2 | ref    | PRIMARY       | PRIMARY      | 5       | table3.id       |     11 | Using where |
|  1 | SIMPLE      | table1 | eq_ref | PRIMARY       | PRIMARY      | 4       | table2.id       |      1 | Using index |
+----+-------------+--------+--------+---------------+--------------+---------+-----------------+--------+-------------+

Is that as good as it can get?

SELECT MIN and MAX of column1 by another distinct column2 and fetch entire row

Ok, so I have the table Television that has over 1,000 records and looks like this:

ID     Code    Source   Brand       Price
-----------------------------------------
930    A584    C11      Panasonic   512
843    VG873   U19      Sony        590
301    A584    J63      Panasonic   494
738    D900    T32      Samsung     378
786    VG873   Y91      Sony        575
409    E764    G48      LG          435
912    VG873   Y91      Sony        535
626    E764    H14      LG          460
581    E764    C55      LG          455
557    D900    I42      Samsung     390

I'm trying to run a query that would fetch the lowest price from each distinct brand where price is greater than or equal to $400, fetching the entire row. The result on the above example set should look like this:

ID     Code    Source   Brand       Price
-----------------------------------------
301    A584    J63      Panasonic   494
409    E764    G48      LG          435
912    VG873   Y91      Sony        535

I tried some answers of somewhat similar questions in here but the results were off by a mile. The last I tried is the following and it was the closest but still not giving the desired result:

SELECT tv.* FROM Television tv
    INNER JOIN (SELECT Brand, MIN(Price) AS MinPrice 
    FROM Television
    GROUP BY Brand) groupedTV
ON tv.Brand= groupedTV.Brand
AND tv.Price= groupedTV.MinPrice
WHERE tv.Price>=400

Any help is appreciated.

Edit: Corrected the result set (ID 301 should be the lowest in Panasonic). Thanks to @wilfo.

Given date or greater than system date validation in mysql (sample query given)

i need to add a where condition something like this below

where future_date='12/31/9999 12:59:59' or future_date > current system date.

How do i do this in my sql?