I have one SQL query where I get some data from DB and I wanna use multiple fetch_assoc inside 2 WHILE loops and also outside WHILE loop:  $test = $db-&gt;prepare("SELECT name FROM test WHERE userid=:userid"); $test-&gt;execute(array(':userid' =&gt; $_GET["id"])); $testing = $test-&gt;fetch(PDO::FETCH_ASSOC);   below this code I have one of the conditions:  if ($testing["name"] == $_POST["name"]) {      echo "Already exist !";  }   and later on the same page, making a list (this is used twice on that page in two different HTML forms):  while ($testing = $test-&gt;fetch(PDO::FETCH_ASSOC)) {         echo "&lt;option&gt; ".$testing["name"]." &lt;/option&gt;";  }   this WHILE doesn't work, because one FETCH_ASSOC already exist.. is there an option, how to make this work without creating a new SQL query ? I need exactly 2 WHILE loops for making a lists, and 1 fetch without loop for protection entry conditions.. and making 3, the exact same SQL queries, seems to me as incorrect approach.. am I wrong ?
120527
I'm trying to write an iterator for results from a PDO statement but I can't find any way of rewinding to the first row. I would like to avoid the overhead of calling fetchAll and storing all the result data.  // first loop works fine foreach($statement as $result) {     // do something with result }  // but subsequent loops don't foreach($statement as $result) {     // never called  }   Is there some way of reseting the statement or seeking the first row?
69918
So I have an SQL dump file that needs to be loaded using mysql_query(). Unfortunately, it's not possible to execute multiple queries with it.  -> It cannot be assumed that the mysql command-line client (mysql --help) is installed -- for loading the SQL file directly  -> It cannot be assumed that the mysqli extension is installed  /* contents of dump.sql, including comments */ DELETE FROM t3 WHERE body = 'some text; with semicolons; scattered; throughout'; DELETE FROM t2 WHERE name = 'hello'; DELETE FROM t1 WHERE id = 1;   The explode() below won't work because some of the dump content's values contain semicolons.  $sql = explode(';', file_get_contents('dump.sql')); foreach ($sql as $key =&gt; $val) {     mysql_query($val); }   What's the best way to load the SQL without modifying the dump file?
721
I'm slowly moving all of my LAMP websites from mysql_ functions to PDO functions and I've hit my first brick wall.  I don't know how to loop through results with a parameter.  I am fine with the following:  foreach ($database-&gt;query("SELECT * FROM widgets") as $results) {    echo $results["widget_name"]; }   However if I want to do something like this:  foreach ($database-&gt;query("SELECT * FROM widgets WHERE something='something else'") as $results) {    echo $results["widget_name"]; }   Obviously the 'something else' will be dynamic.
71555
I've inherited a DB that was lacking a unique constraint so data was/is duplicated. I'm now trying to remove the duplicate records, then add a constraint to block this going forward.  I have this query:  SELECT count(*) as dacount, substr(group_concat(id), (locate(',', group_concat(id))+ 1)) FROM `game` group by matchid, ordinal having dacount &gt; 1 order by dacount desc   which correctly gives me the ids of the rows I need to delete. The problem with this though is that I can't use this as a subquery on the DELETE because of the dacount with the having parameter. Is there another way to do this?  Here was my plan:  DELETE FROM game WHERE id IN (SELECT count(*) as dacount, substr(group_concat(id), (locate(',', group_concat(id))+ 1)) FROM `game` GROUP BY matchid, ordinal HAVING dacount &gt; 1)
111744
Say I have a table called User_FriendList, which has the following characteristics:  CREATE TABLE User_FriendList (     ID ...,     User_ID...,     FriendList_IDs...,     CONSTRAINT User_Friendlist_PK PRIMARY KEY (ID) );   And let us suppose that said table holds the following data:    +----+---------+---------------------------+  | ID | User_ID | Friendlist_IDs            |  +----+---------+---------------------------+  | 1  | 102     | 2:15:66:35:26:17:         |  +----+---------+---------------------------+  | 2  | 114     | 1:12:63:33:24:16:102      |  +----+---------+---------------------------+  | 3  | 117     | 6:24:52:61:23:90:97:118   |  +----+---------+---------------------------+   Note: The “:” (colon) is the delimiter when exploding in PHP into an array.  Questions  So:   Is this a convenient way to “store” the IDs of a FriendList? Or, instead, should I have individual rows with only one single FriendId value in each of them and, when I need to retrieve all the rows of a given list, simply perform a query like SELECT * FROM UserFriendList WHERE UserId = 1?
110550
For a new project that I'm doing in PHP I've created an SQLMethods class to connect to the database and perform queries. Tonight was the first night that I actually got to test it (I wrote it a week or so ago and forgot about it) and an unexpected error occured: When it was calling my ExecuteQuery() function, it wouldn't use the database I selected in the constructor.  The constructor:      public function SQLMethods() {         $SQLConnection = mysql_connect($SQLDBAddress, $SQLUserName, $SQLPassword);          if (!$SQLConnection) {             die('Could not connect: ' . mysql_error());         }          mysql_select_db($SQLDB, $SQLConnection);     }   The function in question:      public function ExecuteQuery($Query) {         mysql_query($Query, $SQLConnection) or die('Could not perform query: ' . mysql_error());     }   Does anyone see what the issue might be? Does the connection close after the constructor completes?
96046
I am having a few issues when people are trying to access a MySQL database and they are trying to update tables with the same information.  I have a webpage written using PHP. In this webpage is a query to check if certain data has been entered into the database. If the data hasn't, then i proceed to insert it. The trouble is that if two people try at the same time, the check might say the data has not been entered yet but when the insert takes place it has been by the other person.  What is the best way to handle this scenario? Can i lock the database to only process my queries first then anothers?
65216
I am dissecting some code and came across this,   $sql = 'SELECT page.*, author.name AS author, updator.name AS updator '      . 'FROM '.TABLE_PREFIX.'page AS page '      . 'LEFT JOIN '.TABLE_PREFIX.'user AS author ON author.id = page.created_by_id '      . 'LEFT JOIN '.TABLE_PREFIX.'user AS updator ON updator.id = page.updated_by_id '      . 'WHERE slug = ? AND parent_id = ? AND (status_id='.Page::STATUS_REVIEWED.' OR status_id='.Page::STATUS_PUBLISHED.' OR status_id='.Page::STATUS_HIDDEN.')';   I am wondering what the "?" does in the WHERE statement.  Is it some sort of parameter holder?
56680
I've seen both the blocks of code in use several different times, personally I have always used the first but my question is: is there a functional difference, and if there is what is it?  while (condition is true ) {     // do something }  do {     // do something } while ( condition is true);   I will be applying this to PHP but I assume this is fairly language agnostic.
44452
I am using MSSQL Server 2005. In my db, I have a table "customerNames" which has two columns "Id" and "Name" and approx. 1,000 results.   I am creating a functionality where I have to pick 5 customers randomly every time. Can anyone tell me how to create a query which will get random 5 rows (Id, and Name) every time  when query is executed?
67515
Ok, here is my dilemma I have a database set up with about 5 tables all with the exact same data structure. The data is separated in this manner for localization purposes and to split up a total of about 4.5 million records.  A majority of the time only one table is needed and all is well. However, sometimes data is needed from 2 or more of the tables and it needs to be sorted by a user defined column. This is where I am having problems.  data columns:  id, band_name, song_name, album_name, genre   MySQL statment:  SELECT * from us_music, de_music where `genre` = 'punk'   MySQL spits out this error:  #1052 - Column 'genre' in where clause is ambiguous   Obviously, I am doing this wrong. Anyone care to shed some light on this for me?
86274
Here's a scenario:  Someone asked about "How to build a SQL query to get count and blah blah blah..."  His only tag is . I added the  and  tags to it.  Someone else answered with a nice SQL statement that has a "group by" statement which seems "logically inevitable".  I proceed to add a tag . I did that on the basis that someone who searches for  will be able to find this post useful, just as I did. My concern is, it wasn't described at all in the question, nor was it implied, unlike  and .  What is the community opinion?  The scenario above is a real one . And the one who answered it nicely is Jon Skeet.
8207
What is the difference between the EXISTS and IN clause in SQL?  When should we use EXISTS, and when should we use IN?
58142
I have a database table that contains a list of contacts, some of those contacts might have multiple records, e.g.  CustomerID, CustomerName, Vehicle 1, Dan, Mazda 1, Dan, Suzuki 2, John, Ford 3, Dasha, Volvo 3, Dasha, Ford   Can I write a select query to return the distinct customerID and CustomerName, and a list of vehicles in 1 record? i.e.  1, Dan, Mazda+Suzuki 2, John, Ford 3, Dasha, Volvo+Ford   Thanks
44395
I have an application written in PHP with Laravel that regularly prepares and executes statements like this:     -- All parameters are varchar(10)    SELECT c1, c2, c3, c4       from MyBigTable      where is_active = 1        and c1 in (@P1, @P2, @P3, @P4 ... @P250)        AND c2 is not NULL   Users have a big data grid, and they can select many rows (there is even a button to select 'ALL'). If they select 250 rows, this statement is what happens. But it takes more than one minute to run, which is unacceptable.  Table MyBigTable has about 10 millions rows. The estimated execution plan shows that 100% of time is spent on an "index seek, non-clustered". From this I deduce that the situation can not be improved using indexes, and that the only issue is in the use of prepared statements. (If you think I am wrong, just let me know). Moreover, I understand that these prepared statements are prepared, used once and discarded, so I don't think they are really beneficial.  What recommendation should I give to the developers?  Should I just tell them to stop using prepared statements, and hardcode the 250 values in the query?  Or should I give them some workaround, like the use of temporary tables (make a temporary table, insert 250 values, then make a query on MyBigTable JOINed with temp)?  Or any other idea?  EDIT: execution plan
116505
From 2012 onward the SQL Server docs show they support  which I'm trying to use instead of a LIMIT.  The following works fine in PostgreSQL to sample a result set,  SELECT * FROM ( VALUES (1),(2),(3) ) AS t(x) OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;   However, with SQL Server, I get  Msg 153, Level 15, State 2, Line 4 Invalid usage of the option FIRST in the FETCH statement.   What's going on here? Does SQL Server support the standardized OFFSET .. FETCH?
90821
If I have a loop running from a category query like :  &lt;?php $the_query = new WP_Query('cat=1&amp;showposts=50&amp;orderby=title&amp;order=asc');?&gt; &lt;ul&gt; &lt;?php while ($the_query-&gt;have_posts()) : $the_query-&gt;the_post();?&gt; &lt;li&gt;.. &lt;/li&gt;&lt;?php wp_reset_query(); ?&gt; &lt;?php endwhile; ?&gt; &lt;/ul&gt;   How would I create an if clause that breaks the list at a certain interval, and starts a new one. So for example at the 10th post, return a &lt;/ul&gt; and start a new one &lt;ul&gt; at 11.  This is incorrect but to illustrate my objective:  &lt;?php $count =0;     while($count &lt;=50){         if ($count == 9){             echo "&lt;li&gt;&lt;a href='&lt;?php the_permalink(); ?&gt;'&gt;                       &lt;?php the_title(); ?&gt;&lt;/a&gt;&lt;/li&gt;&lt;/ul&gt;";             }          elseif ($count == 10){         echo "&lt;ul&gt;&lt;li&gt;&lt;a href='&lt;?php the_permalink(); ?&gt;'&gt;                           &lt;?php the_title(); ?&gt;&lt;/a&gt;&lt;/li&gt;";         }         else {         echo "&lt;li&gt;&lt;a href='&lt;?php the_permalink(); ?&gt;'&gt;&lt;?php the_title(); ?&gt;&lt;/a&gt;&lt;/li&gt;";         }   What is the correct way to include this logic into the loop?