mysql_query

(PHP 4, PHP 5)

mysql_querySend a MySQL query

Warning

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

Description

mixed mysql_query ( string $query [, resource $link_identifier = NULL ] )

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

Parameters

query

An SQL query

The query string should not end with a semicolon. Data inside the query should be properly escaped.

link_identifier

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

Return Values

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

mysql_query() will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query.

Examples

Example #1 Invalid Query

The following query is syntactically invalid, so mysql_query() fails and returns FALSE.

<?php
$result 
mysql_query('SELECT * WHERE 1=1');
if (!
$result) {
    die(
'Invalid query: ' mysql_error());
}

?>

Example #2 Valid Query

The following query is valid, so mysql_query() returns a resource.

<?php
// This could be supplied by a user, for example
$firstname 'fred';
$lastname  'fox';

// Formulate Query
// This is the best way to perform an SQL query
// For more examples, see mysql_real_escape_string()
$query sprintf("SELECT firstname, lastname, address, age FROM friends 
    WHERE firstname='%s' AND lastname='%s'"
,
    
mysql_real_escape_string($firstname),
    
mysql_real_escape_string($lastname));

// Perform Query
$result mysql_query($query);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    
$message  'Invalid query: ' mysql_error() . "\n";
    
$message .= 'Whole query: ' $query;
    die(
$message);
}

// Use result
// Attempting to print $result won't allow access to information in the resource
// One of the mysql result functions must be used
// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.
while ($row mysql_fetch_assoc($result)) {
    echo 
$row['firstname'];
    echo 
$row['lastname'];
    echo 
$row['address'];
    echo 
$row['age'];
}

// Free the resources associated with the result set
// This is done automatically at the end of the script
mysql_free_result($result);
?>

See Also

add a note add a note

User Contributed Notes 57 notes

up
9
SpikeDaCruz
8 years ago
The following function will return the date (on the Gregorian calendar) for Orthodox Easter (Pascha).  Note that incorrect results will be returned for years less than 1601 or greater than 2399. This is because the Julian calendar (from which the Easter date is calculated) deviates from the Gregorian by one day for each century-year that is NOT a leap-year, i.e. the century is divisible by 4 but not by 10.  (In the old Julian reckoning, EVERY 4th year was a leap-year.)

This algorithm was first proposed by the mathematician/physicist Gauss.  Its complexity derives from the fact that the calculation is based on a combination of solar and lunar calendars.

<?php
function getOrthodoxEaster($date){
 
/*
   Takes any Gregorian date and returns the Gregorian
   date of Orthodox Easter for that year.
  */
 
$year = date("Y", $date);
 
$r1 = $year % 19;
 
$r2 = $year % 4;
 
$r3 = $year % 7;
 
$ra = 19 * $r1 + 16;
 
$r4 = $ra % 30;
 
$rb = 2 * $r2 + 4 * $r3 + 6 * $r4;
 
$r5 = $rb % 7;
 
$rc = $r4 + $r5;
 
//Orthodox Easter for this year will fall $rc days after April 3
 
return strtotime("3 April $year + $rc days");
}
?>
up
5
Anonymous
4 years ago
When processing a RENAME TABLE query, PHP apparently always returns false, no matter if the query was successfully processed or not.
up
7
ghotinet
3 years ago
Most spreadsheet programs have a rather nice little built-in function called NETWORKDAYS to calculate the number of business days (i.e. Monday-Friday, excluding holidays) between any two given dates. I couldn't find a simple way to do that in PHP, so I threw this together. It replicates the functionality of OpenOffice's NETWORKDAYS function - you give it a start date, an end date, and an array of any holidays you want skipped, and it'll tell you the number of business days (inclusive of the start and end days!) between them.

I've tested it pretty strenuously but date arithmetic is complicated and there's always the possibility I missed something, so please feel free to check my math.

The function could certainly be made much more powerful, to allow you to set different days to be ignored (e.g. "skip all Fridays and Saturdays but include Sundays") or to set up dates that should always be skipped (e.g. "skip July 4th in any year, skip the first Monday in September in any year"). But that's a project for another time.

<?php

function networkdays($s, $e, $holidays = array()) {
   
// If the start and end dates are given in the wrong order, flip them.   
   
if ($s > $e)
        return
networkdays($e, $s, $holidays);

   
// Find the ISO-8601 day of the week for the two dates.
   
$sd = date("N", $s);
   
$ed = date("N", $e);

   
// Find the number of weeks between the dates.
   
$w = floor(($e - $s)/(86400*7));    # Divide the difference in the two times by seven days to get the number of weeks.
   
if ($ed >= $sd) { $w--; }        # If the end date falls on the same day of the week or a later day of the week than the start date, subtract a week.

    // Calculate net working days.
   
$nwd = max(6 - $sd, 0);    # If the start day is Saturday or Sunday, add zero, otherewise add six minus the weekday number.
   
$nwd += min($ed, 5);    # If the end day is Saturday or Sunday, add five, otherwise add the weekday number.
   
$nwd += $w * 5;        # Add five days for each week in between.

    // Iterate through the array of holidays. For each holiday between the start and end dates that isn't a Saturday or a Sunday, remove one day.
   
foreach ($holidays as $h) {
       
$h = strtotime($h);
        if (
$h > $s && $h < $e && date("N", $h) < 6)
           
$nwd--;
    }

    return
$nwd;
}

$start = strtotime("1 January 2010");
$end = strtotime("13 December 2010");

// Add as many holidays as desired.
$holidays = array();
$holidays[] = "4 July 2010";            // Falls on a Sunday; doesn't affect count
$holidays[] = "6 September 2010";        // Falls on a Monday; reduces count by one

echo networkdays($start, $end, $holidays);    // Returns 246

?>

Or, if you just want to know how many work days there are in any given year, here's a quick function for that one:

<?php

function workdaysinyear($y) {
   
$j1 = mktime(0,0,0,1,1,$y);
    if (
date("L", $j1)) {
        if (
date("N", $j1) == 6)
            return
260;
        elseif (
date("N", $j1) == 5 or date("N", $j1) == 7)
            return
261;
        else
            return
262;
    }
    else {
        if (
date("N", $j1) == 6 or date("N", $j1) == 7)
            return
260;
        else
            return
261;
    }
}

?>
up
4
mwwaygoo at hotmail dot com
2 years ago
I much prefer to use the same syntax for single INSERT, REPLACE and UPDATE queries as it is easier to read and keeps my code shorter (no seperate building of insert and update values)

INSERT INTO table SET x='1', y=3
UPDATE table SET x='2' WHERE y=3

So if your using a function to build your query, you will only ever need to code the "field=value, field2=value2" part for any query.
up
3
ddlshack [at] gmail.dot.com
3 years ago
Use this to neatly insert data into a mysql table:

<?php
function mysql_insert($table, $inserts) {
   
$values = array_map('mysql_real_escape_string', array_values($inserts));
   
$keys = array_keys($inserts);
       
    return
mysql_query('INSERT INTO `'.$table.'` (`'.implode('`,`', $keys).'`) VALUES (\''.implode('\',\'', $values).'\')');
}
?>

For example:

<?php

mysql_insert
('cars', array(
   
'make' => 'Aston Martin',
   
'model' => 'DB9',
   
'year' => '2009',
));
?>
up
3
Just.Kevin
4 years ago
In order to determine if a year is a leap year an earlier poster suggested simply checking to see if the year is a multiple of four:

<?php
function is_leapyear_broken($year = 2004) {
return (
$year%4)==0;
}
?>

While this will work for the majority of years it will not work on years that are multiples of 100 but not multiples of 400 i.e.(2100).
A function not using php's date() function that will also account for this small anomaly in leap years:

<?php
function is_leapyear_working($year = 2004) {
    if(((
$year%4==0) && ($year%100!=0)) || $year%400==0) {
        return
true;
    }
    return
false;
}
?>

While is_leapyear_working will not return true for the few non-leap years divisible by four I couldn't tell you if this is more or less efficient than using php's date() as an even earlier poster suggested:

<?php
function is_leapyear($year = 2004) {
$is_leap = date('L', strtotime("$year-1-1"));
return
$is_leap;
}
?>
up
2
Richie (at) RichieBartlett.com
4 years ago
For those of you whom spent hours bashing your brains against the keyboard wondering why your non-English characters are output as question marks... Try the following:

<?php

$db
= mysql_connect('YOUR_DB_ADDRESS','YOUR_DB_USER','YOUR_DB_PASS') or die("Database error");
mysql_select_db('YOUR_DB', $db);

//SOLUTION::  add this comment before your 1st query -- force multiLanuage support
$result = mysql_query("set names 'utf8'");

$query = "select * from YOUR_DB_TABLE";
$result = mysql_query($query);

//-THE_REST_IS_UP_TO_YOU-

?>

Simply run the query "set names 'utf8' " against the MySQL DB and your output should appear correct.
up
3
Edward Rudd
4 years ago
To actually make use ot the "u" (microsecond) you need to use the DateTime object and not the date() function.

For example

<?php
$t
= microtime(true);
$micro = sprintf("%06d",($t - floor($t)) * 1000000);
$d = new DateTime( date('Y-m-d H:i:s.'.$micro,$t) );

print
$d->format("Y-m-d H:i:s.u");
?>
up
2
mel dot boyce at gmail dot com
8 years ago
I've been flicking through the comments looking for some succinct date code and have noticed an alarming number of questions and over-burdened examples related to date mathematics. One of the most useful skills you can utilize when performing date math is taking full advantage of the UNIX timestamp. The UNIX timestamp was built for this kind of work.

An example of this relates to a comment made by james at bandit-dot-co-dot-en-zed. James was looking for a way to calculate the number of days which have passed since a certain date. Rather than using mktime() and a loop, James can subtract the current timestamp from the timestamp of the date in question and divide that by the number of seconds in a day:
<?php
$days
= floor((time() - strtotime("01-Jan-2006"))/86400);
print(
"$days days have passed.\n");
?>

Another usage could find itself in a class submitted by Kyle M Hall which aids in the creation of timestamps from the recent past for use with MySQL. Rather than the looping and fine tuning of a date, Kyle can use the raw UNIX timestamps (this is untested code):
<?php
$ago
= 14; // days
$timestamp = time() - ($ago * 86400);
?>

Hopefully these two examples of "UNIX-style" timestamp usage will help those finding date mathematics more elusive than it should be.
up
1
Andy McReed
5 years ago
Even though executing multiple queries from one string isn't possible with the mysql_query method there is a way to do it which i found on this guys site (http://www.dev-explorer.com/articles/multiple-mysql-queries). Basically you just explode your SQL string by the semicolon (;) separating the queries and then loop through the resulting array executing each one individually. Saves you from having lines and lines of function calls in your code.
up
2
jc
6 years ago
date("W") returns the iso8601 week number, while date("Y") returns the _current_ year. This can lead to odd results. For example today (dec 31, 2007) it returns 1 for the week and of course 2007 for the year. This is not wrong in a strict sense because iso defines this week as the first of 2008 while we still have 2007.

So, if you don't have another way to safely retrieve the year according to the iso8061 week-date - strftime("%G") doesn't work on some systems -, you should be careful when working with date("W").

For most cases strftime("%W") should be a safe replacement.

[edit: Much easier is to use "o" (lower case O) instead of "Y"]
up
1
eduardo at digmotor dot com dot br
5 years ago
Thanks to tcasparr at gmail dot com for the great idea (at least for me) ;)
I changed the code a little to replicate the functionality of date_parse_from_format, once I don't have PHP 5.3.0 yet. This might be useful for someone. Hope you don't mind changing your code tcasparr at gmail dot com.

<?php
/*******************************************************
 * Simple function to take in a date format and return array of associated
 * formats for each date element
 *
 * @return array
 * @param string $strFormat
 *
 * Example: Y/m/d g:i:s becomes
 * Array
 * (
 *     [year] => Y
 *     [month] => m
 *     [day] => d
 *     [hour] => g
 *     [minute] => i
 *     [second] => s
 * )
 *
 *  This function is needed for  PHP < 5.3.0
 ********************************************************/
function dateParseFromFormat($stFormat, $stData)
{
   
$aDataRet = array();
   
$aPieces = split('[:/.\ \-]', $stFormat);
   
$aDatePart = split('[:/.\ \-]', $stData);
    foreach(
$aPieces as $key=>$chPiece)   
    {
        switch (
$chPiece)
        {
            case
'd':
            case
'j':
               
$aDataRet['day'] = $aDatePart[$key];
                break;
               
            case
'F':
            case
'M':
            case
'm':
            case
'n':
               
$aDataRet['month'] = $aDatePart[$key];
                break;
               
            case
'o':
            case
'Y':
            case
'y':
               
$aDataRet['year'] = $aDatePart[$key];
                break;
           
            case
'g':
            case
'G':
            case
'h':
            case
'H':
               
$aDataRet['hour'] = $aDatePart[$key];
                break;   
               
            case
'i':
               
$aDataRet['minute'] = $aDatePart[$key];
                break;
               
            case
's':
               
$aDataRet['second'] = $aDatePart[$key];
                break;           
        }
       
    }
    return
$aDataRet;
}
?>

Also, if you need to change the format of dates:

<?php
function changeDateFormat($stDate,$stFormatFrom,$stFormatTo)
{
 
// When PHP 5.3.0 becomes available to me
  //$date = date_parse_from_format($stFormatFrom,$stDate);
  //For now I use the function above
 
$date = dateParseFromFormat($stFormatFrom,$stDate);
  return
date($stFormatTo,mktime($date['hour'],
                                   
$date['minute'],
                                   
$date['second'],
                                   
$date['month'],
                                   
$date['day'],
                                   
$date['year']));
}

?>
up
1
Anonymous
5 years ago
Correct format for a MySQL DATETIME column is
<?php $mysqltime = date ("Y-m-d H:i:s", $phptime); ?>
up
1
girishpadia at gmail dot com
5 years ago
Hello all,

I have extended mysql_query function to have more flexibility. It is a php file say "run_query.php". Include this file in your php application. To use this follow this steps.

1) Create a table in mysql with the following structure.

CREATE TABLE `errorlog` (
`query` varchar(5000) default NULL,
`error` varchar(5000) default NULL
)

2) Create a php file and paste following code. Save the file (say file name is run_query.php).

<?php
function mysql_query_($query)
{
    include
"pass1.php";
   
$curdate = date("d-m-Y H:i:s");
    if(
mysql_query($query) == true)
    {
        if(
substr(strtoupper($query),0,6) == 'INSERT' || substr(strtoupper($query),0,5) == 'UPDATE' || substr(strtoupper($query),0,5) == 'DELETE')
        {
           
$fp=fopen("trans.sql","a");
            if(
$fp==null)
            {

                die(
"File cannot be opened. Try again !!!");
            }
           
$printline = "/* $curdate : */ $query ;";
           
fprintf($fp,"\r\n%s",$printline);
           
fclose($fp);
            return
true;
        }
        else
        {
            return
mysql_query($query);
        }
    }
    else
    {
       
$error = mysql_error();
       
$error = addslashes($error);
       
$query = addslashes($query);
       
mysql_query("insert into errorlog values('$query','$error')");
        return
false;
    }
}
?>

3) Include this file in your any php application.
4) Use mysql_query_ function instead of mysql_query function.

Advantages.

1) You can log query and error in your database table (errorlog).

2) A sql file created/appendended whenever any Data manipulation query is fired. So you can have a complete transaction log in query format.

3) This sql file will help you in "point in time recovery" in case of the database is crashed.

Your views ,comments and updation in this function are welcome.

girishpadia@gmail.com
up
1
rogier
6 years ago
For all you programmers out there getting the 'Command out of synch' errors when executing a stored procedure call:

There are known bugs related to this issue, and the best workaround for avoiding this error seems to be switching to mysqli.

Still, I needed mysql to also handle these calls correctly.
The error is normally related to wrong function call sequences, though the bug report at  http://bugs.php.net/bug.php?id=39727 shows otherwise.

For me, after commenting out hundreds of lines and several introspection calls to parse the procedure information (using information_schema and 'SHOW' extensions), I still got the same error.
The first result is returned, because I initiated my connection using the MYSQL_MULTI_RESULTS value of 131072 (forget this and you will never get any output, but an error message stating mysql cannot return results in this context)

After testing with this code (sproc2 simply calls 'SELECT * FROM sometable'), I found the error must be in the mysql library/extension. Somehow, mysql does not handle multiple resultsets correctly, or is at least missing some functionality related to handling multiple results.

<?php
   
//...
   
$rs = mysql_query('CALL sproc2(500)');
    while ((
$row=mysql_fetch_assoc($rs))!==false) {
       
print_r($row);
    }
   
mysql_free_result($rs);

   
$rs = mysql_query('CALL sproc2(500)');
    print
mysql_error(); //the notorious 'command out of synch' message :(
   
while (($row=mysql_fetch_assoc($rs))!==false) {
       
print_r($row);
    }
   
mysql_free_result($rs);
?>

After spending hours debugging my code (the full library is already over the MB), the only solution seemed to be to CLOSE the connection after the first call, and reopening it before the second.

So if you ever make a uniform database accessing interface and implement stored procedures/prepared statements (or classes for it), this could be a solution if you really wish to enable stored procedures.

Still, be aware that this is really a serious flaw in your design (and IMHO, the mysql extension)

Also see the documentation for mysqli on mysqli_query, which seems to be working fine.
up
1
halion at gmail dot com
6 years ago
mysql_query doesnt support multiple queries, a way round this is to use innodb and transactions

this db class/function will accept an array of arrays of querys, it will auto check every line for affected rows in db, if one is 0 it will rollback and return false, else it will commit and return true, the call to the function is simple and is easy to read etc
----------

class MySQLDB
{
   private $connection;          // The MySQL database connection

   /* Class constructor */
   function MySQLDB(){
      /* Make connection to database */
      $this->connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
      mysql_select_db(DB_NAME, $this->connection) or die(mysql_error());
   }

   /* Transactions functions */

   function begin(){
      $null = mysql_query("START TRANSACTION", $this->connection);
      return mysql_query("BEGIN", $this->connection);
   }

   function commit(){
      return mysql_query("COMMIT", $this->connection);
   }
  
   function rollback(){
      return mysql_query("ROLLBACK", $this->connection);
   }

   function transaction($q_array){
         $retval = 1;

      $this->begin();

         foreach($q_array as $qa){
            $result = mysql_query($qa['query'], $this->connection);
            if(mysql_affected_rows() == 0){ $retval = 0; }
         }

      if($retval == 0){
         $this->rollback();
         return false;
      }else{
         $this->commit();
         return true;
      }
   }

};

/* Create database connection object */
$database = new MySQLDB;

// then from anywhere else simply put the transaction queries in an array or arrays like this:

   function function(){
      global $database;

      $q = array (
         array("query" => "UPDATE table WHERE something = 'something'"),
         array("query" => "UPDATE table WHERE something_else = 'something_else'"),
         array("query" => "DELETE FROM table WHERE something_else2 = 'something_else2'"),
      );

      $database->transaction($q);

   }
up
1
axiak at mit dot edu
7 years ago
Gconner at sgi...

your function breaks when you give it a question mark!
Here's a function which correctly implements what I think you want. I'm using it in a pet project of mine.

The code:
<?php
function mysql_prepare ($query, $phs = array()) {
   
$phs = array_map(create_function('$ph',
                    
'return "\'".mysql_real_escape_string($ph)."\'";'), $phs);

   
$curpos = 0;
   
$curph  = count($phs)-1;

    for (
$i=strlen($query)-1; $i>0; $i--) {

      if (
$query[$i] !== '?')  continue;
      if (
$curph < 0 || !isset($phs[$curph]))
   
$query = substr_replace($query, 'NULL', $i, 1);
      else
   
$query = substr_replace($query, $phs[$curph], $i, 1);

     
$curph--;
    }
    unset(
$curpos, $curph, $phs);
    return
$query;
}
?>
up
0
elephant
3 years ago
you can speed up your query by using multiple insert simultaneously using this syntax:

"INSERT INTO table (id,box) VALUES (1,'box1'),(2,'box2'),(3,'box3')"
up
0
lb at bostontech dot net
4 years ago
Not sure why this got ignored the first time, but this is an even simpler way to check leap year:

<?php
function isLeapYear($year)
    { return (((
$year%4==0) && ($year%100)) || $year%400==0) ? (true):(false); }
?>
up
0
tchalvakspam at gmail dot com
5 years ago
Be aware that if a browser isn't accepting cookies, it can cause the session to invisibly not save between two pages.

So for example, in Firefox 3.0.6 there is a bug where "localhost" addresses are currently not saving cookies.  This will kill any attempts to use session code in development where localhost is used.

One workaround for this is to use a different synonym for the localhost address, i.e. http://127.0.0.1/, or set up a virtualhost, e.g. http://localdevelopment/

Overall, pay close attention if you see to different, unexpected session behavior in two different browsers, it may be a result of the cookies being handled differently or being dropped.
up
0
yesmarklapointe at hotmail dot com
5 years ago
Here is some info about how to use SID with session_start().
Experiment run on WAMPserver, PHP version 5.2.6.
Using FireFox 3.0.

The definition found at http://www.php.net/manual/en/session.constants.php 
describes SID is a “pre-defined constant” containing either:
1). the session name and session ID in the form of "name=ID" .
This is the same name as would be returned by session_name(), and the same id as would be returned
by session_id(). 

2).  Or an empty string,  if the session ID was set in an appropriate session cookie.

Example code:
<?php
session_start
();
echo
'<a href="page2.php?' . SID . '">page2</a>';
?>

This code will yield in the browser one of the following:
a).   “page 2”      (as a hyperlink, but mouseover shows the link is to something in the form of
http://www.example.com/page2.php?PHPSESSID=e2bd7072....  )

b).   “page 2”     (as a hyperlink, but mouseover shows http://www.example.com/page2.php? )

Which result is determined by whether or not the browser is sending a matching session cookie back to the script. Result “a” above always appears at least initially since the script hasn’t yet set a cookie and neither has it been done automatically yet by the php.ini setting session.use_cookies. However, after refreshing or using the back button, the form in "b" will appear because then the cookie exists (either you set it or session.use_cookies did it automatically).
up
0
ialsoagree
5 years ago
When you run a select statement and receive a response, the data types of your response will be a string regardless of the data type of the column.

<?php
// Query to select an int column
$query = 'SELECT user_id FROM users WHERE user_id = 1';
$result = mysql_query($query);
$array = mysql_fetch_assoc($result);

// Echoes: string
echo gettype($array['user_id']);
?>
up
0
info at vanylla dot it
5 years ago
If while testing locally on your Windows machine you get many warnigns like:

Warning: session_start()... failed: No such file or directory
Warning: session_start()...: Cannot send session cache limiter - headers already sent
etc.

you need to configure properly the session.save_path in your php.ini file.

Set session.save_path to an existing folder on your PC.
For example: session.save_path="C:\Temp";
up
0
Jessycormier at gmail dot com
5 years ago
I found i was getting a lot of
"Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent "

Warnings, on some pages, where as on other pages it worked just fine.

After some time looking around and trying things like ob_start() and session_write_close()... nothing seemed to work.

Then i realized the encoding of the page. I checked the encoding of the page and it was set to UTF-8, so I changed it to ANSI and now the Warnings went away.

Good luck any one else.
up
0
philippe dot latulippe at xxxxgmail dot com
5 years ago
When using session_start() with cookies over an encrypted connection (like HTTPS) it is important to set php_ini('session.cookie_secure',true).  This sets a flag in the cookie which will stop browsers from sending this cookie over an unsecured connection.  If this is not done, a Man-In-The-Middle could easily cause the victim's browser to send his session ID in plaintext, gaining access to the account and defeating the goal of the secure connection.

http://fscked.org/blog/fully-automated-active-https-cookie-hijacking
up
0
andy_isherwood at hotmail dot com
5 years ago
A session created with session_start will only be available to pages within the directory tree of the page that first created it.

i.e. If the page that first creates the session is /dir1/dir2/index.php and the user then goes to any page above dir2 (e.g. /dir1/index.php), session_start will create a new session rather than use the existing one.
up
0
Anonymous
5 years ago
Relying in external functions doesn't help you in learning programming, that is calling external functions without having an idea of their use.

<?PHP
function mysql_queryf($string)
{
   
$args = func_get_args();
   
array_shift($args);
   
$len = strlen($string);
   
$sql_query = "";
   
$args_i = 0;
    for(
$i = 0; $i < $len; $i++)
    {
        if(
$string[$i] == "%")
        {
           
$char = $string[$i + 1];
           
$i++;
            switch(
$char)
            {
                case
"%":
                   
$sql_query .= $char;
                    break;
                case
"u":
                   
$sql_query .= "'" . intval($args[$args_i]) . "'";
                    break;
                case
"s":
                   
$sql_query .= "'" . mysql_real_escape_string($args[$args_i]) . "'";
                    break;
                case
"x":
                   
$sql_query .= "'" . dechex($args[$args_i]) . "'";
                    break;
            }
            if(
$char != "x")
            {
               
$args_i++;
            }
        }
        else
        {
           
$sql_query .= $string[$i];
        }
    }
    return
mysql_query($sql_query);
}
?>
up
0
lanbotdevman
5 years ago
I modified a bit script originally posted by
ix at nivelzero dot ro
14-Aug-2005 01:07

This allows using large .sql files without getting 'memory size exhausted' error.

<?php
function parse_mysql_dump($url) {
   
   
$handle = @fopen($url, "r");
   
$query = "";
    while(!
feof($handle)) {
       
$sql_line = fgets($handle);
        if (
trim($sql_line) != "" && strpos($sql_line, "--") === false) {
           
$query .= $sql_line;
            if (
preg_match("/;[\040]*\$/", $sql_line)) {
               
$result = mysql_query($query) or die(mysql_error());
               
$query = "";
            }
        }
    }
}
?>
up
0
Mr. Tim
5 years ago
It should be noted that mysql_query can generate an E_WARNING (not documented).  The warning that I hit was when the db user did not have permission to execute a UDF.

Expected behavior would be like an Invalid SQL statement, where there is no E_WARNING generated by mysql_query.

Warning: mysql_query() [function.mysql-query]: Unable to save result set in filename.php

The mysql_errno is 1370 and the mysql_error is:

execute command denied to user 'username'@'%' for routine 'database_name.MyUDF'
up
0
i dot prasoon at gmail dot com
5 years ago
The function formToDB($table, $exceptions = '', $sql_type = 'insert', $sql_condition = NULL) is not working properly. Problem with exceptional fields is there.

        I modified this function and now it is working properly. Here is the changed function -

function formToDB($table, $exceptions = '', $sql_type = 'insert', $sql_condition = NULL) {

  // define some vars
  $fields = '';
  $values = '';
 
  // format input fields into sql
  foreach ($_POST as $field => $value) {
   
    if (!strstr($exceptions,$field)) {
   
      $value = mysql_real_escape_string($value);
      if ($sql_type == 'insert') {
       
        $fields .= "$field, ";
        $values .= "'$value', ";
      }
      else {
        $fields .= "$field = '$value', ";
      }
    }
  }
 
  // remove trailing ", " from $fields and $values
  $fields = preg_replace('/, $/', '', $fields);
  $values = preg_replace('/, $/', '', $values);
 
  // create sql statement
  if ($sql_type == 'insert') {
    $sql = "INSERT INTO $table ($fields) VALUES ($values)";
  }
  elseif ($sql_type == 'update') {
    if (!isset($sql_condition)) {
      echo 'ERROR: You must enter a sql condition!';
      exit;
    }
    $sql = "UPDATE $table SET $fields WHERE $sql_condition";
  }
  else {
    echo 'ERROR: Invalid input for argument $sql_type: must be "insert" or "update"';
    exit;
  }
 
  // execute sql
  if (mysql_query($sql)) {
    return true;
  }
  else {
    //echo mysql_error();
    return false;
  }

}
up
0
JonathanCross.com
5 years ago
<?php
// A demonstration of the new DateTime class for those
// trying to use dates before 1970 or after 2038.
?>
<h2>PHP 2038 date bug demo (php version <?php echo phpversion(); ?>)</h1>
<div style='float:left;margin-right:3em;'>
<h3>OLD Buggy date()</h3>
<?php
  $format
='F j, Y';
  for (
$i = 1900; $i < 2050; $i++) {
   
$datep = "$i-01-01";
   
?>
    Trying: <?php echo $datep; ?> = <?php echo date($format, strtotime($datep)); ?><br>
    <?php
 
}
?></div>
<div style='float:left;'>
  <h3>NEW DateTime Class (v 5.2+)</h3><?php
 
for ( $i = 1900; $i < 2050; $i++) {
   
$datep = "$i-01-01";
   
$date = new DateTime($datep);
   
?>
    Trying: <?php echo $datep; ?> = <?php echo $date->format($format); ?><br>
    <?php
 
}
?></div>
up
0
pascalxusPLEASENOSPAM at yahoo dot com
5 years ago
Here is an example of using the mysql_query in the context of connecting to a database.

<?php
 
function connect( $dbName )
  {
    do {
     
$databaseResponse = mysql_connect(
     
"example.com", "username", "password" );
     
sleep(1);
    } while(
$databaseResponse === false );

    @
$selectResult = mysql_select_db( $dbName ) or dieFunc();
  }

  function
executeQuery( $query, $db )
  {
      if(
$db != "" ) connect( $db );
      else
connect( "pascal_crm" );

     
$result= mysql_query( $query );
     
$err   = mysql_error();
      if(
$err != "" ) echo "error=$err  ";
     
mysql_close();
      return
$result;
  }

?>
up
0
michael at pythontech dot net dot au
5 years ago
@ash

Whilst this may prevent session hijacking attempts, it also ruins the user experience, pressing back and forward will result in the 'security' token being out of sync. Also opening new tabs/ windows will also cause issues.

A better method would be to simply check the ip address or ip block of the user. But this is not without problems either.

To access highly sensitive information, the user should be required to enter his/her password again, even tho they are logged in. This ensures that session hijackers can not access this information.

All this goes hand-in-hand with regenerating session ids on login, and good use of https.
up
0
fernandoleal at loytek dot com
5 years ago
Dunno if is it a bug but when you are working with replications servers and work with multiple databases queries if you don't select the database it will only insert,update,delete into the master and bypass the slave, I think it its because it doesn't insert the sql on the binary log so the work around its to just call mysql_select_db
MYSQL : 5.0.51a-log
PHP: 5.2.6
Example:
<?php
#Inserts only to master
$link=mysql_connect('host','user','pass');
$sql ="INSERT INTO mysql.host (host) VALUES ('localhost');"
var_dump(mysql_query($sql,$link));

#The Working Way Master - Slave
$link2=mysql_connect('host','user','pass');
$select_db = mysql_select_db('mysql', $link2);
var_dump(mysql_query($sql,$link2));  
?>
up
0
ash at atomic-network dot co dot uk
5 years ago
@My last post...
the function will generate the session "token" you just need to do a check and regenerate it on each page load...
up
0
ash at atomic-network dot co dot uk
5 years ago
When developing applications or just creating a general website with the use of sessions, many developers do not think about securing session hijacking attacks. For further information I recommend searching google, however I have produced a small function to be called immediately after session_start().

your_page.php:
<?php
session_start
();
include_once
'session_secure.inc.php';
session_secure();
# Your content here.
?>

session_secure.inc.php :
<?php
function session_secure(){
   
// wrapped for the php entry....
   
$alph =array('A','a','B','b','C','c','D','d','E',
   
'e','F','f','G','g','H','h','I','i','J','K','k',
   
'L','l','M','m','N','n','O','o','P','p','Q','q',
   
'R','r','S','s','T','t','U','u','V','v','W','w',
   
'X','x','Y','y','Z','z');
    for(
$i=0;$i<rand(10,20);$i++){
       
$tmp[] =$alph[rand(0,count($alph))];
       
$tmp[] =rand(0,9);
    }
    return
implode("",shuffle($tmp));
}
?>

There are quicker ways like md5(time()*r
up
0
uramihsayibok, gmail, com
6 years ago
Similar to the queryf() posted by sk89q, I've found this small function quite handy.

Just please, please, PLEASE remember to escape your strings!
<?php

function mysql_queryf($query) {
    if (
func_num_args()>1) {
       
$args=func_get_args();
       
$query=call_user_func_array("sprintf",$args);
    }
    return
mysql_query($query);
}

// Allows for things like
$resultset=mysql_queryf("SELECT * FROM `table` WHERE `id`=%u LIMIT 1", $_GET["id"]);
// $_GET["id"] will be converted to an integer: 0 if it's invalid

?>
With a little modification it can handle a $connection resource too.
up
0
kriskra at gmail dot com
6 years ago
Here is a small neat function to print out the mysql result as a html table:

<?php
function echo_result($result) {
 
?><table><tr><?
  if(! $result) { ?><th>result not valid</th><? }
  else {
    $i = 0;
    while ($i < mysql_num_fields($result)) {
      $meta = mysql_fetch_field($result, $i);
      ?><th style="white-space:nowrap"><?=$meta->name?></th><?
      $i++;
    }
    ?></tr><?
   
    if(mysql_num_rows($result) == 0) {
      ?><tr><td colspan="<?=mysql_num_fields($result)?>">
      <strong><center>no result</center></strong>
      </td></tr><?
    } else
      while($row=mysql_fetch_assoc($result)) {
        ?><tr style="white-space:nowrap"><?
        foreach($row as $key=>$value) { ?><td><?=$value?></td><? }
        ?></tr><?
      }
  }
  ?></table><?
}
?>
up
0
sk89q
6 years ago
sprintf+mysql_query which auto-escapes.

Usage:
<?php
$db
->queryf("SELECT `m`.`name` FROM `test`.`members` WHERE `name`=%s OR `id`=%d OR `sex` IN (%a)", "Evil 'injection'", 'NaN', array('male', 'female', 'both', 'other', "Alien quote'man"));
?>

To be put into a class, and don't forget to change the $this->query() and $this->escape() functions accordingly.

<?php
function queryf($sql)
{   
   
$args = func_get_args();
    @
array_shift($args);
   
   
$this->queryf_args = $args;
   
$this->queryf_i = 0;
   
   
$query = preg_replace_callback("#%(.)#", array($this, 'queryf_format'), $sql);
   
    return
$this->query($query);
}

function
queryf_format($m)
{   
   
$args = $this->queryf_args;
   
$i = &$this->queryf_i;
   
    switch(
$m[1])
    {
        case
'%':
            return
"%";
        case
'a':
           
$s = array();
           
$arr = $args[$i++];
            foreach(
$arr as $x)
            {
               
$s[] = "'".$this->escape($x)."'";
            }
            return
implode(",", $s);;=
        case
's':
            return
"'".$this->escape($args[$i++])."'";
        case
'd':
            return
"".intval($args[$i++])."";
        default:
           
trigger_error("Bad type specifier: {$m[1]}", E_USER_ERROR);
    }
}
?>
up
0
masteracc0 at aol dot com
6 years ago
Keep in mind when dealing with PHP & MySQL that sending a null-terminated string to a MySQL query can be misleading if you use echo($sql) in PHP because the null terminator may not be visible.

For example (this assumes connection is already made),
$string1 = "mystring\0";
$string2 = "mystring";

$query1 = "SELECT * FROM table WHERE mystring='".$string1."'"
$query2 = "SELECT * FROM table WHERE mystring='".$string2."'"
 
$result1 = mysql_query($query1);

$result2 = mysql_query($query2);

//$result1 IS NOT EQUAL TO $result2 but will not provide an error

//but printing these queries to the screen will provide the same result
echo($result1);
echo($result2);

Not knowing this could lead to some mind-numbing troubleshooting when dealing with any strings with a null terminator.  So now you know! :)
up
0
patrick at ciphertek dot com
6 years ago
If you spend a lot of time writing pages that take input from a form and insert it into a database, this function will save you time!

Please Note: You have to name your form fields the same as their corresponding table column is named in mysql for this to work.

<?php

// $table - name of the mysql table you are querying
// $exceptions - fields that will not be inserted into table
//               i.e. 'submit, action, '; (note trailing comma and space!)
// $sql_type - has to be 'insert' or 'update'
// $sql_condition - have to define this if $sql_type = 'update'
//                  i.e. "userID = '".$_POST['userID']."'"
function formToDB($table, $exceptions = '', $sql_type = 'insert', $sql_condition = NULL) {

 
// define some vars
 
$fields = '';
 
$values = '';
 
 
// format input fields into sql
 
foreach ($_POST as $field => $value) {
    if (!
preg_match("/$field, /", $exceptions)) {
     
$value = mysql_real_escape_string($value);
      if (
$sql_type == 'insert') {
       
$fields .= "$field, ";
       
$values .= "'$value', ";
      }
      else {
       
$fields .= "$field = '$value', ";
      }
    }
  }
 
 
// remove trailing ", " from $fields and $values
 
$fields = preg_replace('/, $/', '', $fields);
 
$values = preg_replace('/, $/', '', $values);
 
 
// create sql statement
 
if ($sql_type == 'insert') {
   
$sql = "INSERT INTO $table ($fields) VALUES ($values)";
  }
  elseif (
$sql_type == 'update') {
    if (!isset(
$sql_condition)) {
      echo
'ERROR: You must enter a sql condition!';
      exit;
    }
   
$sql = "UPDATE $table SET $fields WHERE $sql_condition";
  }
  else {
    echo
'ERROR: Invalid input for argument $sql_type: must be "insert" or "update"';
    exit;
  }
 
 
// execute sql
 
if (mysql_query($sql)) {
    return
true;
  }
  else {
   
//echo mysql_error();
   
return false;
  }

}
// end of function formToDB()

// Example for inserting new row
formToDB('users', 'submit, ');

// Example for updating existing row
formToDB('users', 'submit, userID, ', 'update', "userID = '".$_POST['userID']."'");

?>
up
0
ollitech at gmail dot com
6 years ago
Running an invalid delete query may not return false.

Invalid because no such record exists.

Code;

[php]
    //  execute it
                      $result=mysql_query($sql_delete_byindex);
                               
                      if(!$result||$result==FALSE){
                                   
                                                echo("<h1>Error occured while removing listing #: <i>".$rec_index."</i> </H1>");                                   
                                           
                                    }else if($result==TRUE){
                                               
                                                echo("<h1>Listing #: <i>".$rec_index."</i> Deleted!</H1>");
                                   
                                    echo "<a href=\"index.php\">Go to Start Page</a>";
                                   
                                    }
[/php]

Query;
[code]
$sql_delete_byindex = "DELETE FROM `$mysql_table` WHERE `index` = '".$rec_index."' AND `key` = '".$key."'";   
[/code]

result will be TRUE
up
0
jack dot whoami at gmail dot com
6 years ago
Simulating an atomic operation for application locks using mysql.

$link = mysql_connect('localhost', 'user', 'pass');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('foo', $link);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}

$q = "update `table` set `LOCK`='F' where `ID`='1'";
$lock = mysql_affected_rows();

If we assume
     NOT LOCKED = "" (empty string)
     LOCKED = 'F'

then if the column LOCK had a value other than F (normally should be an empty string) the update statement sets it to F and set the affected rows to 1. Which mean than we got the lock.
If affected rows return 0 then the value of that column was already F and somebody else has the lock.

The secret lies in the following statement taken from the mysql manual:
"If you set a column to the value it currently has, MySQL notices this and does not update it."

Of course all this is possible if the all application processes agree on the locking algorithm.
up
0
JustinB at harvest dot org
7 years ago
If you're looking to create a dynamic dropdown list or pull the possible values of an ENUM field for other reasons, here's a handy function:

<?php
// Function to Return All Possible ENUM Values for a Field
function getEnumValues($table, $field) {
   
$enum_array = array();
   
$query = 'SHOW COLUMNS FROM `' . $table . '` LIKE "' . $field . '"';
   
$result = mysql_query($query);
   
$row = mysql_fetch_row($result);
   
preg_match_all('/\'(.*?)\'/', $row[1], $enum_array);
    if(!empty(
$enum_array[1])) {
       
// Shift array keys to match original enumerated index in MySQL (allows for use of index values instead of strings)
       
foreach($enum_array[1] as $mkey => $mval) $enum_fields[$mkey+1] = $mval;
        return
$enum_fields;
    }
    else return array();
// Return an empty array to avoid possible errors/warnings if array is passed to foreach() without first being checked with !empty().
}
?>

This function asumes an existing MySQL connection and that desired DB is already selected.

Since this function returns an array with the original enumerated index numbers, you can use these in any later UPDATEs or INSERTS in your script instead of having to deal with the string values.  Also, since these are integers, you can typecast them as such using (int) when building your queries--which is much easer for SQL injection filtering than a string value.
up
0
massiv at nerdshack dot com
7 years ago
Small change in mysql_dump function, to remove the ";" char at the end of the query.

<?
  function parse_mysql_dump($url, $ignoreerrors = false) {
   $file_content = file($url);
   //print_r($file_content);
   $query = "";
   foreach($file_content as $sql_line) {
     $tsl = trim($sql_line);
     if (($sql_line != "") && (substr($tsl, 0, 2) != "--") && (substr($tsl, 0, 1) != "#")) {
       $query .= $sql_line;
       if(preg_match("/;\s*$/", $sql_line)) {
         $query = str_replace(";", "", "$query");
         $result = mysql_query($query);
         if (!$result && !$ignoreerrors) die(mysql_error());
         $query = "";
       }
     }
   }
  }
?>

... Massimo
up
0
noah at missionecommerce dot com
7 years ago
I got so tired of having to type out all the 11 letters in "mysql_query()" and even more tired of having to iterate through the result set....

So I created the perfect little all purpose wrapper function, called "q()";

<?
function q($query,$assoc=1) {
    $r = @mysql_query($query);
    if( mysql_errno() ) {
        $error = 'MYSQL ERROR #'.mysql_errno().' : <small>' . mysql_error(). '</small><br><VAR>$query</VAR>';
        echo($error); return FALSE;
    }
    if( strtolower(substr($query,0,6)) != 'select' ) return array(mysql_affected_rows(),mysql_insert_id());
    $count = @mysql_num_rows($r);
    if( !$count ) return 0;
    if( $count == 1 ) {
        if( $assoc ) $f = mysql_fetch_assoc($r);
        else $f = mysql_fetch_row($r);
        mysql_free_result($r);
        if( count($f) == 1 ) {
            list($key) = array_keys($f);   
            return $f[$key];
        } else {
            $all = array();
            $all[] = $f;
            return $all;
        }
    } else {
        $all = array();
        for( $i = 0; $i < $count; $i++ ) {
            if( $assoc ) $f = mysql_fetch_assoc($r);
            else $f = mysql_fetch_row($r);
            $all[] = $f;
        }
        mysql_free_result($r);
        return $all;
    }
}

?>

Example:

<?
$r = q('Select id,foo FROM blah');
echo $r[0]['id']; // first row, field 'id'

// for single field single row selects
// only the value is returned
$count = q('SELECT count(*) from blah');
// $count is the number
?>

Returns affected_rows and/or insert_id for anything other than select's. If you dont want field name keys then pass 0 for second parameter.
up
0
veyita_angi at hotmail dot com
7 years ago
this could be a nice way to print values from 2 tables with a foreign key. i have not yet tested correctly but it should work fine.

$buscar = mysql_query("SELECT k.*, e.Clasificacion FROM cat_plan_k k, cat_equipo e WHERE Tipo='$tipo' AND k.ID_Eq=a.ID_Eq");
    while ($row=mysql_fetch_array($buscar))
        {
            $nombre = "e.Clasificacion";
            $row[$nombre] = $Clasific; echo $row[$nombre].'convertido en '.$Clasific;
        }         
    mysql_free_result($buscar);
up
0
cc+php at c2se dot com
7 years ago
Here's a parameterised query function for MySQL similar to pg_query_params, I've been using something similar for a while now and while there is a slight drop in speed, it's far better than making a mistake escaping the parameters of your query and allowing an SQL injection attack on your server.

<?php   # Parameterised query implementation for MySQL (similar PostgreSQL's PHP function pg_query_params)
        # Example: mysql_query_params( "SELECT * FROM my_table WHERE col1=$1 AND col2=$2", array( 42, "It's ok" ) );

       
if( !function_exists( 'mysql_query_params' ) ) {

                function
mysql_query_params__callback( $at ) {
                        global
$mysql_query_params__parameters;
                        return
$mysql_query_params__parameters[ $at[1]-1 ];
                }

                function
mysql_query_params( $query, $parameters=array(), $database=false ) {

                       
// Escape parameters as required & build parameters for callback function
                       
global $mysql_query_params__parameters;
                        foreach(
$parameters as $k=>$v )
                               
$parameters[$k] = ( is_int( $v ) ? $v : ( NULL===$v ? 'NULL' : "'".mysql_real_escape_string( $v )."'" ) );
                       
$mysql_query_params__parameters = $parameters;

                       
// Call using mysql_query
                       
if( false===$database )
                                return
mysql_query( preg_replace_callback( '/\$([0-9]+)/', 'mysql_query_params__callback', $query ) );
                        else    return
mysql_query( preg_replace_callback( '/\$([0-9]+)/', 'mysql_query_params__callback', $query ), $database );

                }
        }

?>
up
0
joe
7 years ago
alteration to the script reposted by
webmaster at vs2055067 dot vserver dot de

   $fields = implode(array_keys($toAdd), ',');
   $values = "'".implode(array_values($toAdd), "','")."'";

should really be

   $fields = "`".implode(array_keys($toAdd), '`,`')."`";
   $values = "'".implode(array_values($toAdd), "','")."'";

as keys like `desc` (short for description) cause errors
up
-1
deepakshahji at ymail dot com
5 months ago
can any help me to connect mysql php with following code
<?php
$empname
=$_POST['$name'];
$empid=10;
$ebp=2500;
$ta=12*$ebp;
echo
$empname;
echo
$empid;
$query ="INSERT INTO emp"(ID,NAME,BP,TA) "VALUES" ('$empid','$empname','$ebp','$ta')
$inserts=mysql_query($query);
if(!
$inserts)
{
$message = 'INVALId query:' .mysql_error()."\n";
$message .= 'whole query' .$query;
die(
$message);
}
while(
$row = mysql_fetch_assoc($inserts))
{
echo
$row["empname"];
echo
$row["empid"];
echo
$row["ebp"];
echo
$row["ta"];
}
?>
<?php
 
include("header.php");
?>