Query returns correct results in MySQL, but not in PHP
Posted: Sat Apr 24, 2010 10:49 pm
I'm a very new php/mysql developer, and although I've searched forums, I don't know that I'm searching for the right thing, so please bear with me if this is a common issue.
My table contains the following data:
[text]+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+
| thread_id | message_id | subject | body | posted_by | post_date | parent_id |
+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+
| 1 | 2 | New Discussion 1 | This is some text for discussion 1. | Esther Strom | 2010-04-24 20:21:43 | 0 |
| 1 | 3 | New Response to message 2 | This is some text for response 1. | Esther Strom | 2010-04-24 20:23:36 | 2 |
| 2 | 4 | New Thread 2 | This is some text for main 2. | Esther Strom | 2010-04-24 20:23:58 | 0 |
| 3 | 5 | New Discussion 3 | This is some text for discussion 3. | Esther Strom | 2010-04-24 20:32:47 | 0 |
| 1 | 6 | Another response to message 2 | This is some text for response 2. | Esther Strom | 2010-04-24 20:33:26 | 2 |
+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+[/text]
I have a very simple select query:
When I run this in my SQL gui (MySQL Control Center) it returns what I expect:
[text]+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+
| thread_id | message_id | subject | body | posted_by | post_date | parent_id |
+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+
| 1 | 2 | New Discussion 1 | This is some text for discussion 1. | Esther Strom | 2010-04-24 20:21:43 | 0 |
| 1 | 3 | New response to message 2 | This is some text for response 1. | Esther Strom | 2010-04-24 20:23:36 | 2 |
| 1 | 6 | Another response to message 2 | This is some text for response 2. | Esther Strom | 2010-04-24 20:33:26 | 2 |
+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+[/text]
However, when I use the same query inside my PHP code, it doesn't pull in the first item (the main message, which has a parent_id of 0.) The page is called from a link that contains an id parameter, like so:
[text]http://127.0.0.1/pcbug/viewDiscussion.php?id=1[/text]
Here's the code in viewDiscussion:
This is the result I get (output HTML code):
message id: 3
posted by: Esther Strom
posted on: 2010-04-24 20:23:36
parent id: 2
body: This is some text for response 1.
message id: 6
posted by: Esther Strom
posted on: 2010-04-24 20:33:26
parent id: 2
body: This is some text for response 2.
There should be another chunk at the very beginning that looks like this:
message id: 2
posted by: Esther Strom
posted on: 2010-04-24 20:10:18
parent id: 0
body: This is some text for discussion 1.
Any ideas? I'm sure this is something very simple, but I have no idea where to even look for an answer.
My table contains the following data:
[text]+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+
| thread_id | message_id | subject | body | posted_by | post_date | parent_id |
+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+
| 1 | 2 | New Discussion 1 | This is some text for discussion 1. | Esther Strom | 2010-04-24 20:21:43 | 0 |
| 1 | 3 | New Response to message 2 | This is some text for response 1. | Esther Strom | 2010-04-24 20:23:36 | 2 |
| 2 | 4 | New Thread 2 | This is some text for main 2. | Esther Strom | 2010-04-24 20:23:58 | 0 |
| 3 | 5 | New Discussion 3 | This is some text for discussion 3. | Esther Strom | 2010-04-24 20:32:47 | 0 |
| 1 | 6 | Another response to message 2 | This is some text for response 2. | Esther Strom | 2010-04-24 20:33:26 | 2 |
+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+[/text]
I have a very simple select query:
Code: Select all
SELECT * FROM discussions where thread_id=1 order by message_id asc[text]+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+
| thread_id | message_id | subject | body | posted_by | post_date | parent_id |
+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+
| 1 | 2 | New Discussion 1 | This is some text for discussion 1. | Esther Strom | 2010-04-24 20:21:43 | 0 |
| 1 | 3 | New response to message 2 | This is some text for response 1. | Esther Strom | 2010-04-24 20:23:36 | 2 |
| 1 | 6 | Another response to message 2 | This is some text for response 2. | Esther Strom | 2010-04-24 20:33:26 | 2 |
+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+[/text]
However, when I use the same query inside my PHP code, it doesn't pull in the first item (the main message, which has a parent_id of 0.) The page is called from a link that contains an id parameter, like so:
[text]http://127.0.0.1/pcbug/viewDiscussion.php?id=1[/text]
Here's the code in viewDiscussion:
Code: Select all
<?php require_once("connectvars.php"); //contains db_ variables
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die ( "Can't connect to the MySQL Server/Database" );
if ( isset($_GET['id']) && is_int((int)$_GET['id']) ) {
//we have an id in the URL
$query = "SELECT * FROM discussions where thread_id=".$_GET['id'] . " order by message_id asc" ;
$result = mysqli_query($dbc, $query) or die('Error querying database');
$rows = mysqli_fetch_array($result);
mysqli_close($dbc);
} //end if
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>pcbug - discussions - <?php echo $rows['subject']; ?></title>
</head>
<body>
<?php
while ( $rows = mysqli_fetch_array($result) ) {
echo "<p>message id: " .$rows['message_id']. "<br/>";
echo "posted by: " . $rows[ 'posted_by']. "<br/>";
echo "posted on: " . $rows[ 'post_date']. "<br/>";
echo "parent id: " . $rows[ 'parent_id']. "<br/>";
echo "body: " . $rows[ 'body']. "</p>";
}
?>
</body>
</html>
message id: 3
posted by: Esther Strom
posted on: 2010-04-24 20:23:36
parent id: 2
body: This is some text for response 1.
message id: 6
posted by: Esther Strom
posted on: 2010-04-24 20:33:26
parent id: 2
body: This is some text for response 2.
There should be another chunk at the very beginning that looks like this:
message id: 2
posted by: Esther Strom
posted on: 2010-04-24 20:10:18
parent id: 0
body: This is some text for discussion 1.
Any ideas? I'm sure this is something very simple, but I have no idea where to even look for an answer.