The daily news ( February 14, 1900 )Set Page Title using PHP - Stack Overflow
to customize your list.
Join the Stack Overflow Community
Stack Overflow is a community of 6.7 million programmers, just like you, helping each other.
J it only takes a minute:
I wish to set the title of my webpage to Ultan.me - Whatever the post title. I want it to display the post title. The posts are submitted to a MySQL database and the title row is called "title". Any help is appreciated with this small question.
Here is the page itself now but it doesn't display the title. Should I open the php document and connect to my database somewhere different to it's current locations?
The Code (The only necessary piece is the beginning):
&meta name="keywords" content="Mac user Ultan Casey TheCompuGeeks UltanKC"&
&title&Ultan.me - &?echo $?&&/title&
&link rel="stylesheet" href="css/styles.css" type="text/css" /&
&script type="text/javascript"
src="/ajax/libs/jquery/1.2.6/jquery.min.js"&&/script&
&script type="text/javascript"
src="js/jquery.labelify.js"&&/script&
&script type="text/javascript"&
$(document).ready(function(){
$(":text").labelify();
a {text-decoration:none}
&div id="main"&
&!-- Menu Start --&
&div id="menu"&
&li&&a href="index.php"&home&/a&&/li&
&li&&a href="index.php"&about me&/a&&/li&
&li&&a href="index.php"&archives&/a&&/li&
&li&&a href="index.php"&contact&/a&&/li&
&li&&a href="index.php"&gallery&/a&&/li&
&!-- Menu End --&
&img src="images/banner.png" /&
&div id="content"&
&div id="posts"&
mysql_connect ('localhost', 'root', 'root') ;
mysql_select_db ('ultankc');
if (!isset($_GET['id']) || !is_numeric($_GET['id'])) {
die("Invalid ID specified.");
$id = (int)$_GET['id'];
$sql = "SELECT * FROM php_blog WHERE id='$id' LIMIT 1";
$result = mysql_query($sql) or print ("Can't select entry from table php_blog.&br /&" . $sql . "&br /&" . mysql_error());
while($row = mysql_fetch_array($result)) {
$date = date("l F d Y", $row['timestamp']);
$title = stripslashes($row['title']);
$entry = stripslashes($row['entry']);
$get_categories = mysql_query("SELECT * FROM php_blog_categories WHERE `category_id` = $row[category]");
$category = mysql_fetch_array($get_categories);
&p&&?php echo "&p id='post-title'&&strong&&a href=\"post.php?id=". $id . "\"&" . $title . "&/a&&/strong&&/p&"; ?&&br /&&br /&
&div id="entry"&&?php echo $ ?&
&/div&&br /&&br /&
&p id="date"&Posted in &a href="category.php?category=&?php echo $row['category']; ?&"&&?php echo $category['category_name']; ?&&/a& on &?php echo $ ?&&/p&
&h2 id="share-title"&Share This Post&/h2&
&div id="social-share"&
&li id="link-right"&&a href="/home?status=
I just read &?php echo $ ?& at http://ultan.me/post.php?id=&?php echo $ ?&"&&center&Twitter&/center&&/a&&/li&
&li id="link-left"&&a href="/submit?url=http://ultan.me/post.php?id=&?php echo $ ?&"&&center&Digg&/center&&/a&&/li&
&li id="link-right"&&a href="/sharer.php?u=http://ultan.me/post.php?id=&?php echo $ ?&&t=&?php echo $ ?&"&&center&Facebook&/center&&/a&&/li&
&li id="link-left"&&a href="/buzz/post?url=http://ultan.me/post.php?id=&?php echo $ ?&
"&&center&Google Buzz&/center&&/a&&/li&
&div class="clr"&&/div&
&h2 id="comments-title"&Comments&/h2&
&div id="comment-list"&
$commenttimestamp = strtotime("now");
$sql = "SELECT * FROM php_blog_comments WHERE entry='$id' ORDER BY timestamp";
$result = mysql_query ($sql) or print ("Can't select comments from table php_blog_comments.&br /&" . $sql . "&br /&" . mysql_error());
while($row = mysql_fetch_array($result)) {
$timestamp = date("l F d Y", $row['timestamp']);
printf("&div class='comment-ind'&&p id='comments'&&a id='username' href=\"%s\"&%s&/a& %s&/p&", stripslashes($row['url']), stripslashes($row['name']), $timestamp);
print("&p class='comments'&" . stripslashes($row['comment']) . "&/p&&div class='clr'&&br&&/div&&/div&");
&div class="clr"&&/div&
&form id="commentform" method="post" action="process.php"&
&p&&input type="hidden" name="entry" id="entry" value="&?php echo $ ?&" /&
&input type="hidden" name="timestamp" id="timestamp" value="&?php echo $ ?&"&
&input type="text" name="name" id="name" title="Name (required)" /&&br /&
&input type="text" name="email" id="email" title="Mail (will not be published) (required)" /&&br /&
&input type="text" name="url" id="url" title="Website" value="http://" /&&br /&
title="Your Comment Goes Here" name="comment" id="comment"&&/textarea&&/p&
&p&&input type="submit" name="submit_comment" id="submit_comment" value="Add Comment" /&&/p&
&div id="pages"&
$total_results = mysql_fetch_array(mysql_query("SELECT COUNT(*) AS num FROM php_blog"));
$total_pages = ceil($total_results['num'] / $blog_postnumber);
if ($page & 1) {
$prev = ($page - 1);
echo "&a href=\"?page=$prev\"&&&
Newer&/a& ";
for($i = 1; $i &= $total_ $i++) {
if ($page == $i) {
echo "$i ";
echo "&a href=\"?page=$i\"&$i&/a& ";
if ($page & $total_pages) {
$next = ($page + 1);
echo "&a href=\"?page=$next\"&Older &&&/a&";
&!-- Sidebar Start --&
&div class="sidebar"&
&!-- Item 1 --&
&div id="side-item"&
&a href="/UltanCasey"&
&img src="images/db-icon.jpg"&Dailybooth
&div id="side-item-content"&
&img src="/UltanCasey/latest/medium.jpg" /&
&!-- Item 2 --&
&div id="side-item"&
&h2&&img src="images/connect.jpg" /&Connect&/h2&
&div id="side-item-content"&
&div class="tweet-title"&&p&&a href="/UltanKc"&Latest Tweet:&/a&&/p&&/div&
&div id="tweet"&
function getTwitterStatus($userid){
$url = "/statuses/user_timeline/$userid.xml?count=1";
function auto_link_twitter ($text)
// properly formatted URLs
$urls = "/(((http[s]?:\/\/)|(www\.))?(([a-z][-a-z0-9]+\.)?[a-z][-a-z0-9]+\.[a-z]+(\.[a-z]{2,2})?)\/?[a-z0-9._\/~#&=;%+?-]+[a-z0-9\/#=?]{1,1})/is";
$text = preg_replace($urls, " &a href='$1'&$1&/a&", $text);
// URLs without protocols
$text = preg_replace("/href=\"www/", "href=\"http://www", $text);
// Twitter usernames
$twitter = "/@([A-Za-z0-9_]+)/is";
$text = preg_replace ($twitter, " &a href='/$1'&@$1&/a&", $text);
// Twitter hashtags
$hashtag = "/#([A-Aa-z0-9_-]+)/is";
$text = preg_replace ($hashtag, " &a href='http://hashtags.org/$1'&#$1&/a&", $text);
$xml = simplexml_load_file($url) or die("could not connect");
foreach($xml-&status as $status){
$text = $status-&
echo auto_link_twitter ($text);
getTwitterStatus("UltanKC");
&li id="social"&&a href="#"&YouTube&/a&&/li&
&li id="social"&&a href="#"&Twitter&/a&&/li&
&li id="social"&&a href="#"&LastFM&/a&&/li&
&li id="social"&&a href="#"&Email&/a&&/li&
&!-- Item 2 End--&
&div id="side-item"&
&h2&&img src="images/archive.jpg" /&Archives&/h2&
&div id="archive-side"&
mysql_connect ('localhost', 'root', 'root') ;
mysql_select_db ('ultankc');
$result = mysql_query("SELECT FROM_UNIXTIME(timestamp, '%Y') AS get_year, COUNT(*) AS entries FROM php_blog GROUP BY get_year");
while ($row = mysql_fetch_array($result)) {
$get_year = $row['get_year'];
$entries = $row['entries'];
echo "&li id='tag'&&a href=\"archives.php?year=" . $get_year . "\"&Entries from " . $get_year . "
(" . $entries . ")&br /&&/a&&/li&";
$result1 = mysql_query("SELECT * FROM php_blog_categories ORDER BY category_name ASC");
while($row = mysql_fetch_array($result1)) {
$result2 = mysql_query("SELECT COUNT(`id`) AS entries FROM php_blog WHERE category = $row[category_id]");
$num_entries = mysql_fetch_array($result2);
echo '&li id="tag"&&a href="category.php?category=' . $row['category_id'] . '"&' . $row['category_name'] . ' (' . $num_entries['entries'] . ')&/a&&/li&';
&div class="clr" /&
&!-- Sidebar End
&div id="footer"&
&p& & Ultan Casey 2010&/p&
&p style="margin-top: -18 float:right"&&a href="index.php"&Home&/a& | &a href="about.php"&About Me&/a& | &a href="mailto:"&Email Me&/a&&/p&
user319815
Here's the method I use (for similar things, not just title):
ob_start (); // Buffer output
&!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"&
&html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"&
&title&&!--TITLE--&&/title&
$pageTitle = 'Title of Page'; // Call this in your pages' files to define the page title
$pageContents = ob_get_contents (); // Get all the page's HTML into a string
ob_end_clean (); // Wipe the buffer
// Replace &!--TITLE--& with $pageTitle variable contents, and print the HTML
echo str_replace ('&!--TITLE--&', $pageTitle, $pageContents);
PHP usually works be executing any bits of code and printing all output directly to the browser. If you say "echo 'Some text here.';", that string will get sent the browser and is emptied from memory.
What output buffering does is say "Print all output to a buffer. Hold onto it. Don't send ANYTHING to the browser until I tell you to."
So what this does is it buffers all your pages' HTML into the buffer, then at the very end, after the
tag, it uses ob_get_contents () to get the contents of the buffer (which is usually all your page's HTML source code which would have been sent the browser already) and puts that into a string.
ob_end_clean () empties the buffer and frees some memory. We don't need the source code anymore because we just stored it in $pageContents.
Then, lastly, I do a simple find & replace on your page's source code ($pageContents) for any instances of '' and replace them to whatever the $pageTitle variable was set to. Of course, it will then replace &title&&!--TITLE--&&/title& with Your Page's Title. After that, I echo the $pageContents, just like the browser would have.
It effectively holds onto output so you can manipulate it before sending it to the browser.
Hopefully my comments are clear enough.
Look up ob_start () in the php manual (
) if you want to know exactly how that works (and you should) :)
20.3k2393150
You parse the field from the database as usual.
Then let's say you put it in a variable called $title, you just
&title&Ultan.me - &?php echo htmlspecialchars($title);?&&/title&
I see your problem. You have to set $title BEFORE using it. That is, you should query the database before &title&...
90.7k22168288
9,84173063
Move the data retrieval at the top of the script, and after that use:
&title&Ultan.me - &?php echo htmlspecialchars($title, ENT_QUOTES, 'UTF-8'); ?&&/title&
154k36274348
You need to set the value of $title before echoing it.
Also, you should really sanitize any data before using it in queries as this is a security risk
The problem is that $title is being referenced on line 5 before it's being assigned on line 58. Rearranging your code isn't easy, because the data is both retrieved and output at the same time. Just to test, how does
Because you're only retrieving one row, you don't need to use a while loop, but I left it with hopes that it'll make it easier for you to relate to your current code. All I've done is removed the actual output from your data retrieval, and added variables for category and category name which are then referred to as usual later on. Also, I haven't tested this. :)
It'll be tricky to rearrange your code to make this work, but I'll try :)
So, put this at the top of your code:
&?php require_once('mysql.php'); ?&
The top of the file should look like:
&?php require_once('mysql.php'); ?&
&meta name="keywords" content="Mac user Ultan Casey TheCompuGeeks UltanKC"&
&title&Ultan.me - &?php echo htmlspecialchars($title); ?& &/title&
Then, create a file called mysql.php in the same directory that the file which contains the code you quoted is in.
Put this is mysql.php:
mysql_connect ('localhost', 'root', 'root');
mysql_select_db ('ultankc');
if (!isset($_GET['id']) || !is_numeric($_GET['id'])) {
die("Invalid ID specified.");
$id = (int)$_GET['id'];
$sql = "SELECT * FROM php_blog WHERE id='$id' LIMIT 1";
$result = mysql_query($sql) or print ("Can't select entry from table php_blog.&br /&" .
$sql . "&br /&" . mysql_error());
$res = mysql_fetch_assoc($result);
$date = date("l F d Y", $res['timestamp']);
$title = $res['title'];
$entry = $res['entry'];
$get_categories = mysql_query("SELECT * FROM php_blog_categories WHERE `category_id` = $res['category']");
$category = mysql_fetch_array($get_categories);
Well, hope that helped :)
90.7k22168288
user179169
&?php echo APP_TITLE?& - &?php echo $page_?&
this should work fine for you
I know this is an old post but having read this I think this solution is much simpler (though technically it solves the problem with Javascript not PHP).
&title&Ultan.me - Unset&/title&
&script type="text/javascript"&
function setTitle( text ) {
document.title =
&!-- other head info --&
// Make the call to the DB to get the title text. See OP post for example
$title_text = "Ultan.me - DB Title";
// Use body onload to set the title of the page
print "&body onload=\"setTitle( '$title_text' )\"
// Rest of your code here
print "&p&Either use php to print stuff&/p&";
&p&or just drop in and out of php&/p&
// close the html page
print "&/body&&/html&";
your header.php has the title tag set to &title&%TITLE%&/title&; the "%" are important since hardly anyone types %TITLE% so u can use that for str_replace() later.
then, u use output buffer like so
ob_start();
include("header.php");
$buffer=ob_get_contents();
ob_end_clean();
$buffer=str_replace("%TITLE%","NEW TITLE",$buffer);
and that should do it.
For more reference, click
6,96261543
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
rev .24947
Stack Overflow works best with JavaScript enabledKeyboard Shortcuts?
Next menu item
Previous menu item
Previous man page
Next man page
Scroll to bottom
Scroll to top
Goto homepage
Goto search(current page)
Focus search box
Change language:
Brazilian Portuguese
Chinese (Simplified)
mysql_query
mysql_query & Envoie une requête à un serveur MySQL
Avertissement
Cette extension
était obsolète en PHP 5.5.0, et a été supprimée en PHP 7.0.0. ? la place, vous pouvez
utiliser l'extension
ou l'extension
. Voir aussi
du guide et ces
pour plus d'informations.
Alternatives à cette fonction :
Description
mysql_query
( string $query
[, resource $link_identifier = NULL
Liste de paramètres
Une requête SQL
La cha?ne de requête ne doit pas se terminer par un point-virgule.
Les données contenues dans la requête doivent être .
link_identifier
La connexion MySQL.
S'il n'est pas spécifié, la dernière connexion ouverte avec la fonction
sera utilisée. Si une telle connexion
n'est pas trouvée, la fonction tentera d'ouvrir une connexion, comme si
la fonction
avait été appelée sans argument.
Si aucune connexion n'est trouvée ou établie, une alerte
E_WARNING est générée.
Valeurs de retour
Pour les requêtes du type SELECT, SHOW,
DESCRIBE, EXPLAIN et les autres requêtes retournant un
jeu de résultats, mysql_query() retournera une
en cas de succès,
ou FALSE en cas d'erreur.
Pour les autres types de requêtes, INSERT, UPDATE,
DELETE, DROP, etc.,
mysql_query() retourne TRUE en cas de succès
ou FALSE en cas d'erreur.
La ressource de résultat retournée doit être passée à la fonction
, et les autres fonctions
permettant d'explorer le résultat des tables, pour accéder aux
données retournées.
pour trouver le nombre
de lignes retournées pour une requête du type SELECT ou
pour trouver le nombre
de lignes affectées par les requêtes du type DELETE,
INSERT, REPLACE, ou UPDATE.
mysql_query() échouera et retournera FALSE
si l'utilisateur n'a pas les autorisations nécessaires pour accéder à la (aux) table(s)
référencée(s) par la requête.
Exemple #1 Requête invalide
La requête suivante est syntaxiquement invalide, donc
mysql_query() va échouer et retourner FALSE.
&?php$result&=&mysql_query('SELECT&*&WHERE&1=1');if&(!$result)&{&&&&die('Requête&invalide&:&'&.&mysql_error());}?&
Exemple #2 Requête valide
La requête suivante est valide, donc mysql_query()
va retourner une .
&?php//&Ceci&peut&être&demandé&par&un&utilisateur,&par&exemple&:$firstname&=&'fred';$lastname&&=&'fox';//&Formulation&de&la&requête//&C'est&la&meilleur&fa?on&d'exécuter&une&requête&SQL//&Pour&plus&d'exemples,&voir&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));//&Exécution&de&la&requête$result&=&mysql_query($query);//&Vérification&du&résultat//&Ceci&montre&la&requête&envoyée&à&MySQL&ainsi&que&l'erreur.&Utile&pour&déboguer.if&(!$result)&{&&&&$message&&=&'Requête&invalide&:&'&.&mysql_error()&.&"\n";&&&&$message&.=&'Requête&complète&:&'&.&$query;&&&&die($message);}//&Utilisation&du&résultat//&Tenter&d'affichager&$result&ne&vous&donnera&pas&d'informations&contenues&dans&la&ressource//&Une&des&fonctions&MySQL&de&résultat&doit&être&utilisée//&Voir&aussi&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'];}//&Libération&des&ressources&associées&au&jeu&de&résultats//&Ceci&est&effectué&automatiquement&à&la&fin&du&scriptmysql_free_result($result);?&
Voir aussi
- Ouvre une connexion & un serveur MySQL
- Retourne le texte associ& avec l'erreur g&n&r&e
lors de la derni&re requ&te
- Prot&ge une commande SQL de la pr&sence de caract&res sp&ciaux
- Retourne un champ d'un r&sultat MySQL
- Lit une ligne de r&sultat MySQL dans un tableau associatif
- Ex&cute une requ&te SQL sans mobiliser les r&sultats MySQL
mysql_query doesnt support multiple queries, a way round this is to use innodb and transactionsthis 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 $& & & & & // 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();& & & &&& & & }else{& & & && $this-&commit();& & & &&& & & }&& }};/* 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 $& & & $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);&& }
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.
Use this to neatly insert data into a mysql table:&?phpfunction 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:&?phpmysql_insert('cars', array(& & 'make' =& 'Aston Martin',& & 'model' =& 'DB9',& & 'year' =& '2009',));?&
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 = 'SELECT user_id FROM users WHERE user_id = 1';$result = mysql_query($query);$array = mysql_fetch_assoc($result);echo gettype($array['user_id']);?&
here's a script for parsing a *.sql file (tested only on dumps created with phpMyAdmin) which is short and simple (why do people say "here's a short and simple script" and it has a 100 lines?). the script skips
to be present within the querys&?php& function parse_mysql_dump($url){& & $file_content = file($url);& & $query = "";& & foreach($file_content as $sql_line){& & & 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 = "";& & & & }& & & }& & }& }?&
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.phpThe mysql_errno is 1370 and the mysql_error is:execute command denied to user 'username'@'%' for routine 'database_name.MyUDF'
If, like me, you come from perl, you may not like having to use sprintf to 'simulate' placeholders that the DBI package from perl provides. I have created the following wrapper function for mysql_query() that allows you to use '?' characters to substitute values in your DB queries. Note that this is not how DBI in perl handles placeholders, but it's pretty similar.&?php& & function mysql_prepare ($query, $phs = array()) {& & & & foreach ($phs as $ph) {& & & & & & $ph = "'" . mysql_real_escape_string($ph) . "'";& & & & & & $query = substr_replace(& & & & & & & & $query, $ph, strpos($query, '?'), 1& & & & & & );& & & & }& & & & return mysql_query($query);& & }& & list($user, $passwd) = array('myuser', 'mypass');& & $sth = mysql_prepare(& & & & 'select userid from users where userid=? and passwd=?',& & & & array($user, sha1($passwd))& & );& & $row = mysql_fetch_row($sth);& & if ($row !== false) {& & & & echo "logging in as '{$row[0]}'!\n";& & }& & else {& & & & echo "Invalid username and password combination.\n";& & }?&
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 resultecho($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! :)
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] = $C echo $row[$nombre].'convertido en '.$C& & & & }& & & & & & & mysql_free_result($buscar);
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
Note that the 'source' command used in the mysql client program is *not* a feature of the server but of the client.This means that you cannot do&& mysql_query('source myfile.sql');You will get a syntax error. Use LOAD DATA INFILE as an alternative.
Until this function prohibits them, watch out for SQL comments (--) in your input.
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-logPHP: 5.2.6Example:&?php$link=mysql_connect('host','user','pass');$sql ="INSERT INTO mysql.host (host) VALUES ('localhost');"var_dump(mysql_query($sql,$link));$link2=mysql_connect('host','user','pass');$select_db = mysql_select_db('mysql', $link2);var_dump(mysql_query($sql,$link2));&& ?&
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 argument2) 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.
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&
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(); 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.
I believe there is a typo in celtic at raven-blue dot com version with:if (($sql != "") && (substr($tsl, 0, 2) != "--") && (substr($tsl, 0, 1) != "#")) {I think you really ment: if (($tsl != "") && (substr($tsl, 0, 2) != "--") && (substr($tsl, 0, 1) != "#")) {I changed the $sql to $tsl
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=3UPDATE table SET x='2' WHERE y=3So 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.
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:
$db = mysql_connect('YOUR_DB_ADDRESS','YOUR_DB_USER','YOUR_DB_PASS') or die("Database error");
mysql_select_db('YOUR_DB', $db);
$result = mysql_query("set names 'utf8'");
$query = "select * from YOUR_DB_TABLE";
$result = mysql_query($query);
Simply run the query "set names 'utf8' " against the MySQL DB and your output should appear correct.
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&& 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 ) {& & & & & & & & & & & & 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;& & & & & & & & & & & & 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 );& & & & & & & & }& & & & }?&
The following query is not valid as expected:&?php$username = 'dicteworld';$username{4} = '';$sql = "SELECT * FROM `user` WHERE `User` = '$username'";print($sql); $res = mysql_query($query);$row = mysql_fetch_array($res);print_r($row);?&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.
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.HTHAllen
When processing a RENAME TABLE query, PHP apparently always returns false, no matter if the query was successfully processed or not.
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.
I think it's important to note (for newbies, like me especially) that an empty result is not the same as an error:&?php$rs = mysql_query("SELECT `foo` FROM `bar`")if($rs) {& echo mysql_num_rows($rs); }
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) {& & & && & & };& & };& };& return $query_}