Why am I returning empty records when querying in mysql with php?
I created the following script to query a table and return the first 30 results. The query returns 30 results, but they do not have any text or information. Why would this be?
The table stores Vietnamese characters. The database is mysql4.
Here's the page: http://saomaidanang.com/recentposts.php
Here's the code:
<?php
header( 'Content-Type: text/html; charset=utf-8' );
//CONNECTION INFO
$dbms = 'mysql';
$dbhost = 'xxxxx';
$dbname = 'xxxxxxx';
$dbuser = 'xxxxxxx';
$dbpasswd = 'xxxxxxxxxxxx';
$conn = mysql_connect($dbhost, $dbuser, $dbpasswd ) or die('Error connecting to mysql');
mysql_select_db($dbname , $conn);
//QUERY
$result =开发者_开发知识库 mysql_query("SET NAMES utf8");
$cmd = 'SELECT * FROM `phpbb_posts_text` ORDER BY `phpbb_posts_text`.`post_subject` DESC LIMIT 0, 30 ';
$result = mysql_query($cmd);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html dir="ltr">
<head>
<title>recent posts</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
<p>
<?php
//DISPLAY
while ($myrow = mysql_fetch_row($result))
{
echo 'post subject:';
echo(utf8_encode($myrow ['post_subject']));
echo 'post text:';
echo(utf8_encode($myrow ['post_text']));
}
?>
</p>
</body>
Try using mysql_fetch_assoc() instead of mysql_fetch_row()
- The former returns an array with each result column stored in an array offset, starting at offset 0.
- The later returns an associative array of strings where the column as used as keys.
And in your program you are using the result as an associative array:
$myrow ['post_subject']
or you could try mysql_fetch_object() and access the data as $myrow->post_subject. If you feel the data is still not displayed, then do a print_r($myrow) to the the output. This way you will know for sure if the data has been returned.
If you use mysql_fetch_row(), you're going to need to access your data through numbered keys (ie $row[0], $row[1], etc). mysql_fetch_object() is the norm today in most applications to fetching rows as mentioned by Shamly.
I'm not a big fan of using PHP's provided functions to do daily coding. Instead, considering writing (or downloading) a class or a few functions to help do the job for you, like:
function my_query($query){
$return = array(); // stuff to return
$result = mysql_query($query);
while($row = mysql_fetch_object($result)){
array_push($return, $row);
}
return $return;
}
So then all you have to do is this in your script:
// connect to db, fill in the appropriate arguments...
$link = mysql_connect(...);
// selecting database
mysql_select_db($dbname, $link);
// do the query
$rows = my_query(...);
foreach($rows as $row){
print_r($row); // see what's in there...
}
By using the my_query() function, you save yourself a few steps. This is also particularly useful doing nested queries within loops. What's easier to read?
// assume database is already connected
$result = mysql_query('SELECT user_id FROM table');
while($row = mysql_fetch_object($result)){
$result_2 = mysql_query('SELECT * FROM other_table WHERE user_id = '.$row->user_id);
while($row_2 = mysql_fetch_object($result_2)){
// do stuff with both rows
// potential for lots of confusion and mysql errors
}
}
or this...
// assume database is already connected
$user_ids = my_query('SELECT user_id FROM table');
foreach($user_ids as $x){
$more_data = my_query('SELECT * FROM other_table WHERE user_id = '.$x->user_id);
foreach($more_data as $y){
// do stuff
}
}
Anyway, probably a much longer answered than you expected, but I hope it gives you an idea of how to go about things :) If you have time, install Wordpress and learn how to use its built in $wpdb object. You will gain a wealth of knowledge of how databases are used in real life applications and you'll be able to make your own database class to suit your needs.
DISCLAIMER: I just wrote all the above code for this post without testing for syntax errors. I apologize if there are any.
use:
while ($myrow = mysql_fetch_array($result))
{ ... }
it will work
加载中,请稍侯......
精彩评论