PHP: mysql_query

User Contributed Notes
mysql_query

webmaster at demogracia dot com
09-May-2005 02:35


Please ignore my previous note. My test script failed on Friday but works fine on Monday :-S



<?

// Test script



$con=mysql_connect('localhost', 'test', 'test');

mysql_select_db('test', $con);

$res=mysql_query('select * from data', $con) or die(mysql_error());

mysql_close($con);



echo 'Sleeping... Please stop MySQL daemon now<br>';

sleep(30);



if($res){

    while($row=mysql_fetch_assoc($res)){

        var_dump($row);

        echo "<br>";

    }

}else{

    echo '<br>Resource not available';

}



echo '<br>Finished';



?>

webmaster at demogracia dot com
06-May-2005 09:13


The output of the query is cached in the MySQL server. Every time you call mysql_fetch_assoc() one row is transmitted from MySQL server to your PHP script. That means the connection is kept open while $resource exists. Also, if MySQL dies before you fetch all rows your script will fail.

wjyong at sh163 dot net
30-Apr-2005 06:21


The following query is not valid as expected:

<?php

$username = 'dicteworld';

$username{4} = '';

$sql = "SELECT * FROM `user` WHERE `User` = '$username'";

print($sql); // Result: SELECT * FROM `user` WHERE `User` = 'dictworld'

$res = mysql_query($query);

$row = mysql_fetch_array($res);

print_r($row);// Result: still return Array(), supposed that the user 'dictworld' exists.

?>

Pay more attention that null string '' is equivalent to '\0',therefore SQL statement above is equivalent to SELECT * FROM `user` WHERE `User` = 'dict\0world',though printing string is right.

diogo at interagir dot org dot br
16-Feb-2005 06:08


Just a very simple way to dump a table:



<?php



$table = mysql_query("SELECT * FROM mytable");

echo "<pre>";

while ($row = mysql_fetch_assoc($table)) {

    print_r($row);

    echo "<br>";

}

echo "</pre>";



?>

jon at websandbox dot net
25-Jan-2005 07:25


I think it's important to note (for newbies, like me especially) that an empty result is not the same as an error:

<?php

/* 'bar' is an empty table in the db */

$rs = mysql_query("SELECT `foo` FROM `bar`")

if($rs) {

  echo mysql_num_rows($rs); //outputs: 0

}



/* malformed query /*

$rs = mysql_query("SELECT `foo` FRO `bar`");

if($rs) {

  echo "This will never be echoed";

}

?>

andregodin at gmail dot com
18-Nov-2004 03:03


Another "dumping" function but with the optional possibility to choose wich field_name to be dumped.  "Have Fun and please email me if you do optimization of this code"



<?php

function mysql_format($strTemp){

    //Format sql sentence for insert

    $bad_chars= array("\\", "'", "\"");

    $good_chars = array("\\\\", "''", "\"\"");

    return str_replace($bad_chars, $good_chars, $strTemp);

}



function mysql_dump_table(){

    /*******************\

    *  MYSQL_DUMP_TABLE  *********************************\

    *  Param�tres :                                       *

    *    1- Table Name                                     *

    *   2- Field(s) (in string format) Name to be dumped  *

    *       If empty, all field will be dumped            *

    \******************************************************/



    if (!(func_num_args())) die ("<b>mysql_dump_table</b>: Need At Least A Table Name");



    $arg_list = func_get_args();

    $arrReturn = "";

    $strTblName = mysql_format("`{$arg_list[0]}`");

    $strFields = "*";



    if (func_num_args() > 1){

        $strFields = "";

        for ($noArg=1; $noArg<func_num_args(); $noArg++){

            if ($strFields) $strFields .= ", ";

            $strFields .= "`$arg_list[$noArg]`";

        }

    }



    $result = mysql_query("SELECT $strFields FROM $strTblName") or die ("Incapable d'ex�cuter la requ�te");



    $nbRecord = 0;

    while ($row = mysql_fetch_assoc($result)){

        $strFieldsNames = "";

        $strFieldsValues = "";



        foreach ($row as $field_name => $field_value){

            if ($strFieldsNames) $strFieldsNames .= ", ";

            $strFieldsNames .= "`$field_name`";



            if($strFieldsValues) $strFieldsValues .= ", ";

            $strFieldsValues .= "'" . mysql_format($field_value) . "'";

        }



        $arrReturn[$nbRecord++] = "INSERT INTO $strTblName($strFieldsNames) values($strFieldsValues);";

    }

    mysql_free_result($result);

    return $arrReturn;

}



require_once("config_connexion_db_test.php");



/****************

* AUTRE EXEMPLE  **********************************************

*    Vous pouvez d�cider d'afficher quelques champs seulements *

*  en sp�cifiant les champs d�sir�                            *

**************************************************************/

$db = mysql_connect(DBTEST_HOST, DBTEST_USER, DBTEST_PASSWORD) or die("Impossible de se connecter : ");

mysql_select_db(DBTEST_NAME, $db);



$result = mysql_dump_table("tbl_produits", "code_produit", "description");



foreach ($result as $sentence){

    //Afficher chaque �l�ment du tableau

    print "$sentence<br>";

}

mysql_close($db);



//Retourne

/********************************************************

INSERT INTO `tbl_produits`(`code_produit`,`description`) VALUES('produit1', 'don\'t have description');

INSERT INTO `tbl_produits`(`code_produit`,`description`) VALUES('produit2', 'without \"description\" too');

INSERT INTO `tbl_produits`(`code_produit`,`description`) VALUES('produit3', '1\\3 more than before');

...

...

*********************************************************/



?>

PHP is a very nice language! 

Andr� Godin :)

me at harveyball dot com
11-Sep-2004 03:13


Just thought id post this as i couldnt find a nice and simple way of dumping data from a mysql database and all the functions i found were way overly complicated so i wrote this one and thought id post it for others to use.



//$link is the link to the database file

//$db_name is the name of the database you want to dump

//$current_time is just a reference of time()



//returns $thesql which is a string of all the insert into statements



function dumpData()

{

global $link,$db_name,$current_time;

$thesql="";

$thesql.="#SQL DATA FOR $mdb_name \n";

$thesql.="#BACK UP DATE ". date("d/m/Y G:i.s",$current_time)." \n";

$result = mysql_list_tables($mdb_name); 

while ($row = mysql_fetch_row($result))

    {

    $getdata=mysql_query("SELECT * FROM $row[0]");

    while ($row1=mysql_fetch_array($getdata))

        {

        $thesql.="INSERT INTO `$row[0]` VALUES (";

        $getcols = mysql_list_fields($mdb_name,$row[0],$link);

            for($c=0;$c<mysql_num_fields($getcols);$c++)

                {

                if (strstr(mysql_field_type($getdata,$c),'blob')) $row1[$c]=bin2hex($row1[$c]);

                //Binary null fix if ever needed

                if ($row1[$c]=="0x") $row1[$c]="0x1";



                //delimit the apostrophies for mysql compatability

                $row1[$c]=str_replace("'","''",$row1[$c]); 

                if (strstr(mysql_field_type($getdata,$c),'blob')) 

                $thesql.="0x$row1[$c]";

                else

                $thesql.="'$row1[$c]'";

                if ($c<mysql_num_fields($getcols)-1) $thesql.=",";

                }

            $thesql.=");;\n";

        }

    }

return $thesql;    

}



Please note the sql statements are terminated with ;; not a ; this is so when you want to do a multiple query you can tokenise the sql string with a ;; which allows your data to contain a ;



If you want to run the multiple query then use this simple function which i wrote due to not being able to find a decent way of doing it



//$q is the query string ($thesql returned string)

//$link is the link to the database connection

//returns true or false depending on whether a single query is executed allows you to check to see if any queries were ran



function multiple_query($q,$link)

    {

    $tok = strtok($q, ";;\n");

    while ($tok)

        {

        $results=mysql_query("$tok",$link);

        $tok = strtok(";;\n");

        }

    return $results;

    }

mark @t ilisys dot c0m au
13-Jul-2004 11:05


The default mysql max_allowed_packet setting is in fact 16MB. The gotcha you can fall into is your text field type. 

eg:

TEXT    Variable-length string up to 65,535 characters long.

MEDIUMTEXT    Variable-length string up to 16,777,215 characters long.

LONGTEXT    Variable-length string up to 4,294,967,295 characters long.

(corresponding sizes for blob, mediumblob, longblob)

diag at chREMOVEeerful dot com
22-Jun-2004 11:54


On mysql 4.0.0-alpha-NT, using float as column type in my case as part of a two-field primary key, will after inserting/updating the 0.66 not find the record with key 0.66 when searched for by key=0.66 (but with: key>0.65 AND key<0.67).



Solution: use DECIMAL(3,2) (or bigger).

samm at os2 dot ru
18-May-2004 04:15


Small correction to the klync post (18-Jan-2004 09:05)

instead 



foreach ( explode(";", "$sql_query") as $sql_line) {



its better to use 



foreach ( explode(";\n", "$sql_query") as $sql_line) {



to avoid errors on strings lines like:

INSERT INTO `cms_text` VALUES ('test; test');

jlsalinas at spamsucks dot gmx dot net
13-Feb-2004 10:23


In response to Johann Eckert (11-Feb-2004 11:08), I think there is a better way to look for repeated entries.



SELECT column, count(column) as c

    FROM table

    GROUP BY column

    HAVING (c > 1);



In this case, we are interested in *only* repeated entries. We can remove the HAVING clause if we also want to get the entries with just one occurrence.



We can also add a ORDER BY c DESC clause to get the most repeated entries first.



Last but not least, if 'c' doesn't work in the HAVING and ORDER clauses (I've never had that problem, but I suspect it may happen with some DBMS), try substituting it with 'count(column)'.

klync-
18-Jan-2004 11:05


I couldn't use mysql_query() to initialize a database the way you can dump a file in from the command line (i.e. `mysql -u db_user -p my_database < dbfile.sql`).



in the end, I had to read the file into a string:



$dbf_handle = fopen($db_file, "r");

$sql_query = fread($dbf_handle, filesize($db_file));

fclose($dbf_handle);



and then I used explode to go through each line of the dumpfile:



foreach ( explode(";", "$sql_query") as $sql_line) {

     if ( $dbquery = mysql_query("$sql_line") ) {



....



Great for a db installer script.

Predrag Supurovic
30-Nov-2003 06:52


If you need to execute sevaral SQL commands in a row (usually called batcg SQL) using PHP you canot use mysql_query() since it can execute single command only.



Here is simple but effective function that can run batch SQL commands. Take cere, if string contains semicolon (;) anywhere except as command delimiter (within string expression for example) function will not work.



function mysql_exec_batch ($p_query, $p_transaction_safe = true) {

  if ($p_transaction_safe) {

      $p_query = 'START TRANSACTION;' . $p_query . '; COMMIT;';

    };

  $query_split = preg_split ("/[;]+/", $p_query);

  foreach ($query_split as $command_line) {

    $command_line = trim($command_line);

    if ($command_line != '') {

      $query_result = mysql_query($command_line);

      if ($query_result == 0) {

        break;

      };

    };

  };

  return $query_result;

}

chris at hotmail dot com
30-Apr-2003 08:28


Windows programmers, keep in mind that although table names in Windows queries are not case sensitive, many *NIX versions of Mysql require the correct table name case (perhaps others as well). So you're better off using the right case from the beginning, in case you ever decide to go with a *NIX server.

davidc at edeca dot net
19-Apr-2003 02:30


Regarding the idea for returning all possible values of an enum field, the mySQL manual says that "SHOW COLUMNS FROM table LIKE column" should be used to do this.



The function below (presumes db connection) will return an array of the possible values of an enum.



function GetEnumValues($Table,$Column)

    {

    $dbSQL = "SHOW COLUMNS FROM ".$Table." LIKE '".$Column."'";

    $dbQuery = mysql_query($dbSQL);



    $dbRow = mysql_fetch_assoc($dbQuery);

    $EnumValues = $dbRow["Type"];



    $EnumValues = substr($EnumValues, 6, strlen($EnumValues)-8); 

    $EnumValues = str_replace("','",",",$EnumValues);



    return explode(",",$EnumValues);

    }



Cavaets:



1) If the LIKE matches more than one column you get the enum from the first, so be careful with the $Column argument

2) You can't have ',' as part of one of the enums (I guess mySQL would escape this, but I haven't tried)

3) If the field isn't an enum you'll get garbage back!



This is just a quick example to show how to do it, some tidying up needs to be done (ie checking if the field is actually an enum) before it is perfect.

09-Apr-2003 02:43


Until this function prohibits them, watch out for SQL comments (--) in your input.

allen a brooker gb net
28-Mar-2003 07:35


One way to reduce the dangers of queries like the dlete command above that dletes the whole DB is to use limits wherever possible. 



EG. If you have a routine that is only deisnged to delete 1 record, add 'LIMIT 1' to the end of the command. This way you'll only lose one record if someone does something stupid.



You should also check all input, especially if it is sent using GET. ie. make sure that $_GET['id'] is not NULL or == "", is a number that is positive, not 0 (generally, I know this doesn't apply to some table types, but it applies to the default) and is within the valid range for that field.



Just don't trust ANY data that is sent to your script.



HTH

Allen

antony%40terian%2Eco%2Euk
27-Mar-2003 09:50


A useful URL for queries to search for duplicate records is: http://www.onlamp.com/pub/a/onlamp/excerpt/mysqlckbk/index1.html?page=last

amchargue44 at hotmail dot com
20-Mar-2003 02:34


If you want a good multiple query function, just look at the source of phpMyAdmin ; particularly read_dump.php . A bit longer than what is above, but more solid I should imagine.

Bobie
26-Jan-2003 06:01


When trying to INSERT or UPDATE and trying to put a large amount of text or data (blob) into a mysql table you might run into problems. 



In mysql.err you might see: 

Packet too large (73904) 



To fix you just have to start up mysql with the option -O max_allowed_packet=maxsize 



You would just replace maxsize with the max size you want to insert, the default is 65536

php at mereck dot net
19-Jan-2003 10:59


phpinfo at t-online dot de,



good point about the security hole potential in multple query handling. I just wanted to say that in some cases its a good idea to use multiple queries for performance issues. For example, according to http://www.mysql.com/doc/en/Insert_speed.html :



"If you are inserting many rows from the same client at the same time, use multiple value lists INSERT statements. This is much faster (many times in some cases) than using separate INSERT statements."

claude_minette at hotmail dot com
18-Nov-2002 06:15


I nedded to have a select box containing the fields of an ENUM in a MySQL table.



Use this if you need it too. it may be useful. Be sure to be connected and use $table to choose the table you want to describe.



$table="Ma_table"

$describe=mysql_query("describe $table");

while ($ligne=mysql_fetch_array($describe)){

    extract($ligne);

    if (substr($Type,0,4)=='enum'){

        echo $Type;

        $liste=substr($Type,5,strlen($Type));

        $liste=substr($liste,0,(strlen($liste)-2));

        $enums=explode(',',$liste);

        if (sizeof($enums)>0){

            echo "<select name='enum'>\n";

            for ($i=0; $i<sizeof($enums);$i++){

                $elem=strtr($enums[$i],"'"," ");

                echo "<option value='".$elem."'>".$elem."</option>\n";

            }

            echo "</select>";

        }

    }

}



Hope it will help...

Some improvements can bee done, if you do, please mail me the improvements you've done...



Min's

acloutier at agricom dot ca
16-Nov-2001 05:03


You can put multiple query with PHP && MySQL:





//initialise MySQL users variables


mysql_query("select @a:=0,@b:=0");





//select values of variables


mysql_query("select @a:=sum(rate), @b:=count(*) from ratetable where rated_user_id='$id'");


 


//finally, select for result    


$rate_id_select=mysql_query("select @c:=round(@a/@b) as effective_rate");


       


//..and result $rate_user=mysql_result($rate_id_select,0,"effective_rate");





...





echo $rate_user;





Alain Cloutier


webmaster@saguenay.cc

nikhil-php at nols dot com
02-Jan-2000 02:38


When trying to INSERT or UPDATE and trying to put a large amount of text or data (blob) into a mysql table you might run into problems.





In mysql.err you might see:


Packet too large (73904)





To fix you just have to start up mysql with the option -O max_allowed_packet=maxsize 





You would just replace maxsize with the max size you want to insert, the default is 65536

mysql_query

Description

Parameters

Return Values

Examples

See Also