PHP MYSQL Interview Question & Books download

Thursday, April 9, 2009

Access method of MySql used in PHP

<?php
/* do a MySQL query */
function do_query ($query, $db_link) {

  $result = @mysql_query($query, $db_link);
  if (!$result) {
      fatal_error("A database query error has occurred!");
  } else {
      return($result);
  }

}

/* get single result value */
function query2result ($query, $db_link) {

  $result = do_query($query, $db_link);
  $row = @mysql_result($result, 0);
  return($row);

}

/* get result in numeric array */
function query2array ($query, $db_link) {

  $result = do_query($query, $db_link);
  $row = @mysql_fetch_row($result);
  return($row);

}

/* get result in associative array */
function query2hash ($query, $db_link) {

  $result = do_query($query, $db_link);
  $row = @mysql_fetch_array($result);
  return($row);

}

/* get row of result */
function result2row ($result) {

  $row = @mysql_fetch_row($result);
  return($row);

}

/* get row of result in hash */
function result2hash ($result) {

  $row = @mysql_fetch_array($result);
  return($row);

}

/* find number of rows in query result */
function number_rows ($result) {

  $number_rows = @mysql_num_rows($result);
  return($number_rows);

}

/* put rows from a column into an array */
function column2array ($query, $db_link) {

  $result = do_query($query, $db_link);
  while ($row = @mysql_fetch_array($result)) {
      $array[] = $row[0];
  }
  return($array);

}
?>

Tuesday, April 7, 2009

rename() function renames a file or directory

<?php
rename("images","pictures");
?>

Parameter          Description
oldname          Required. Specifies the file or directory to be renamed
newname          Required. Specifies the new name of the file or directory
context          Optional. Specifies the context of the file handle. Context is a set of options that can modify the behavior of a stream

Friday, March 27, 2009

Backup Your MySQL Database Using PHP

<?php backup_tables('localhost','userid','password','dbname');

/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{

$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);

//get all of the tables
if($tables == '*')
{
$tables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result))
{
$tables[] = $row[0];

}
print_r($tables);
}

else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}

//cycle through
foreach($tables as $table)
{
$result = mysql_query('SELECT * FROM '.$table);
$num_fields = mysql_num_fields($result);

$return.= 'DROP TABLE '.$table.';';
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
$return.= "\n\n".$row2[1].";\n\n";

for ($i = 0; $i < $num_fields; $i++)
{
while($row = mysql_fetch_row($result))
{
$return.= 'INSERT INTO '.$table.' VALUES(';
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = ereg_replace("\n","\\n",$row[$j]);
if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
if ($j<($num_fields-1)) { $return.= ','; }
}
$return.= ");\n";
}
}
$return.="\n\n\n";
}

//save file
$handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
fwrite($handle,$return);
fclose($handle);
}
?>

Thursday, March 12, 2009

mysql_connect vs. mysql_pconnect

pconnects are expensive in RAM. Make sure you have enough to handle all the children you need and you're ok. total size of an average mysql child process, then subtract the amount of shared memory it uses and you have an approximate delta. Typical mysql is depending on the query you are running, are anywhere from 1 to 10 megs.

Friday, March 6, 2009

Displaying PHP SessionID

<?php
session_start();
?>
</HEAD>
<style type="text/css">

</style>
<BODY>
<strong>Displaying PHP SessionID by Tutorial Guide by http://www.phpmysqlquestion.blogspot.com/
</strong>
<h1 align="center" class="style2">Your PHPSESSID is: <?php echo session_id(); ?></h1>
<span class="style5">NOTE</span>: If you don't see a session id in tbe blue header above, hit <span

class="style3">REFRESH</span> on your browser <br />
<br />
<div align="center">Turotial by <a

href="http://www.phpmysqlquestion.blogspot.com/">http://www.phpmysqlquestion.blogspot.com/</a></div>
<p>
</BODY>
</HTML>

Friday, February 27, 2009

Difference between session-register and $_SESSION in PHP

Difference between session-register and $_SESSION in PHP

I read in the help http://www.php.net/session_register that using $_SESSION[var]=value is better than using session_register. It is because session_register() only works if register_globals is set ON in your php.ini file, but for security purposes this has been disabled by default since PHP 4.2.0 You should therefore use $_SESSION['var'] instead.

Wednesday, February 25, 2009

Using SELECT REPLACE with MySQL

I recently needed to compare the content of two columns in a MySQL database that stored the large and small images for a blog post. All the small ones start with "small" followed by a number and the large ones "big" followed by a number. Enter the REPLACE() function to get rid of small/large and do the comparison! In this post I show how to use the replace function in MySQL.

The REPLACE() function takes three parameters:

the string or column name to do the replacement on
what to look for
and what to replace it with

The following example replaces the 'aaa' part of 'aaa bbb ccc' with 'xyz' and the column returned from the SQL query will contain 'xyz bbb ccc':

SELECT REPLACE('aaa bbb ccc', 'aaa', 'xyz');

If you were doing this against the column "foo" you would do this instead:

SELECT REPLACE(foo, 'aaa', 'xyz');

My example

In my case I had a column called 'image_small' and 'image_large' with example data like so:

+------------+--------------+-------------+
| content_id | image_small  | image_large |
+------------+--------------+-------------+
|          1 | small1.jpg   | big1.jpg    |
|         26 | small26.jpg  | big26.jpg   |
|         27 | small27.jpg  | big27.gif   |
|         24 | small24.jpg  | big24.jpg   |
|        419 | small208.gif | big419.gif  |
+------------+--------------+-------------+

I wanted to replace 'small' with an empty string and 'big' with an empty string in the select query, and then see if they were the same in each column. This can be done with the following query:

SELECT content_id,     REPLACE( image_small, 'small', '' ) AS image_small,     REPLACE( image_large, 'big', '' ) AS image_large FROM content

The resulting data looks like this:

+------------+-------------+-------------+
| content_id | image_small | image_large |
+------------+-------------+-------------+
|          1 | 1.jpg       | 1.jpg       |
|         26 | 26.jpg      | 26.jpg      |
|         27 | 27.jpg      | 27.gif      |
|         24 | 24.jpg      | 24.jpg      |
|        419 | 208.gif     | 419.gif     |
+------------+-------------+-------------+

Extending my example with IF()

The only problem with the above example is that I now need to scan every row and see which ones do and dont' match. The query can be extended with an IF() to output 1 or 0 if the filenames (without the small and big parts) match.

SELECT content_id,
  REPLACE(image_small, 'small', '') as image_small,
  REPLACE(image_large, 'big', '') as image_large,
  IF(REPLACE(image_small, 'small', '') = REPLACE(image_large, 'big', ''), 1, 0) AS matches
FROM content

This adds an extra column called "matches" which will display 1 if the two filenames match or 0 if they don't:

+------------+-------------+-------------+---------+
| content_id | image_small | image_large | matches |
+------------+-------------+-------------+---------+
|          1 | 1.jpg       | 1.jpg       |       1 |
|         26 | 26.jpg      | 26.jpg      |       1 |
|         27 | 27.jpg      | 27.gif      |       0 |
|         24 | 24.jpg      | 24.jpg      |       1 |
|        419 | 208.gif     | 419.gif     |       0 |
+------------+-------------+-------------+---------+

Now it's much easier to see which ones have the same filenames, once the 'small' and 'big' text has been removed by combining the REPLACE() and IF() MySQL functions.