Newbie needs help - code returning empty results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Newbie needs help - code returning empty results

Post by Sinemacula »

I'm not 100% sure whether the issue is with my sql query, or my php code (since I'm not great with either), but the following code is resulting in a table, with all cells empty. I'm hoping the error is something simple, like I've missed a semi-colon or something...

Code: Select all

<?php
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db) or die("<P>Query failed: ".mysql_error());
$result = mysql_query("SELECT CONCAT(t3.lastname, t3.firstname, t2.stored_questionnaire_ID, t1.questionnaire_name, t2.stored_questionnaire_date) AS Link FROM qst_questionnaires AS t1, qst_stored_questionnaires AS t2, mos_comprofiler AS t3 WHERE t1.questionnaire_ID = t2.questionnaire_ID AND t3.user_id = t2.user_ID",$db);
echo "<center>You are viewing the completion information for the assessments<br /><br />";
echo "<TABLE BORDER=\"2\" cellpadding=\"5\">";
echo "<TR><TD><B>First Name</B><TD><B>Last Name</B><TD><B>Questionnaire</b><TD><B>Completed On</B></TR>";
while ($myrow = mysql_fetch_array($result))
{
echo "<TR><TD>".$myrow["t3.firstname"]."<TD>".$myrow["t3.lastname"]."<TD>".$myrow["t1.questionnaire_name"]."<TD align=\"center\">".$myrow["t2.stored_questionnaire_date"];;
}
echo "</TABLE></center>";
?>
The table is created, with the correct number of rows (which leads me to believe the sql query might be okay), but all the cells are empty.

Any ideas where I've gone wrong?

Thanks,
Scott
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

You didn't pull out t3.firstname etc etc.... you used CONCAT in your query and aliased it as `Link` so the only things returned as $myrow['Link']

Change your query to pull out individual values if that's what you need ;)
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

Ah, okay... I think (I've pulled the query from another application I'm using - where it does work - and trying to adapt it to a slightly different context - obviously, I don't know quite what I'm doing - :oops: )

So, I've made some changes:

Code: Select all

$result = mysql_query(&quote;SELECT CONCAT(t3.lastname, t3.firstname, t1.questionnaire_name, t2.stored_questionnaire_date) FROM qst_questionnaires AS t1, qst_stored_questionnaires AS t2, mos_comprofiler AS t3 WHERE t1.questionnaire_ID = t2.questionnaire_ID AND t3.user_id = t2.user_ID&quote;,$db);
But I'm still getting the same output.

I've also tried it without the "CONCAT" and parentheses - same output.

Obviously, I generally learn from trial and error - lot's of error! :wink: I've tried to figure it out from the MySQL manual, but end up getting lost in detail.

So, where is this going wrong?

Thanks,
Scott
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Post your SQL table structure....

Code: Select all

SELECT
    mos_comprofiler.lastname AS lastname,
    mos_comprofiler.firstname AS firstname,
    qst_questionnaires.questionnaire_name AS q_name,
    qst_stored_questionnaires.stored_questionnaire_date AS q_date
FROM
    qst_questionnaires AS t1,
    qst_stored_questionnaires AS t2,
    mos_comprofiler AS t3
WHERE
    t1.questionnaire_ID = t2.questionnaire_ID
AND
    t3.user_id = t2.user_ID
Note: Drop the dot syntax in the field names if those field names are unique across all tables you are pulling data from.
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

Thank you for the code... I'm now getting only the first row of the table, with the hard coded headings.

Here's what I'm using:

Code: Select all

<?php
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db) or die("<P>Query failed: ".mysql_error());
$result = mysql_query("SELECT
    mos_comprofiler.lastname AS lastname,
    mos_comprofiler.firstname AS firstname,
    qst_questionnaires.questionnaire_name AS q_name,
    qst_stored_questionnaires.stored_questionnaire_date AS q_date
FROM
    qst_questionnaires AS t1,
    qst_stored_questionnaires AS t2,
    mos_comprofiler AS t3
WHERE
    t1.questionnaire_ID = t2.questionnaire_ID
AND
    t3.user_id = t2.user_ID",$db);
echo "<center>You are viewing the completion information for the assessments<br /><br />";
echo "<TABLE BORDER=\"2\" cellpadding=\"5\">";
echo "<TR><TD><B>First Name</B><TD><B>Last Name</B><TD><B>Questionnaire</b><TD><B>Completed On</B></TR>";
while ($myrow = mysql_fetch_array($result))
{
echo "<TR><TD>".$myrow["firstname"]."<TD>".$myrow["lastname"]."<TD>".$myrow["q_name"]."<TD align=\"center\">".$myrow["q_date"];;
}
echo "</TABLE></center>";
?>

I'm not exactly sure of the proper format for writing the SQL table structure... I could post the SQL dump of the full structure for those three tables. The relevant parts are:

mos_comprofiler
- user_id
- firstname
- lastname

qst_questionnaires
- questionnaire_name
- questionnaire_ID

qst_stored_questionnaires
- questionnaire_ID
- user_ID
- stored_questionnaire_date

And I'm trying to end up with a list that is:
firstname, lastname, questionnaire_name, stored_questionnaire_date

Let me know if you want the whole table structure dump.

Thank you for your help!!

Scott
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Post by Sinemacula »

I decided to try one more thing before giving up for the night...

I took what you had suggested, and sort of combined it with what I started with, and got this:

Code: Select all

<?php
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db) or die("<P>Query failed: ".mysql_error());
$result = mysql_query("SELECT
    t3.lastname AS lastname,
    t3.firstname AS firstname,
    t1.questionnaire_name AS q_name,
    t2.stored_questionnaire_date AS q_date
FROM
    qst_questionnaires AS t1,
    qst_stored_questionnaires AS t2,
    mos_comprofiler AS t3
WHERE
    t1.questionnaire_ID = t2.questionnaire_ID
AND
    t3.user_id = t2.user_ID",$db);
echo "<center>You are viewing the completion information for the assessments<br /><br />";
echo "<TABLE BORDER=\"2\" cellpadding=\"5\">";
echo "<TR><TD><B>First Name</B><TD><B>Last Name</B><TD><B>Questionnaire</b><TD><B>Completed On</B></TR>";
while ($myrow = mysql_fetch_array($result))
{
echo "<TR><TD>".$myrow["firstname"]."<TD>".$myrow["lastname"]."<TD>".$myrow["q_name"]."<TD align=\"center\">".$myrow["q_date"];;
}
echo "</TABLE></center>";
?>
And just much to my surprise, and joy, it worked! :D 8)

Thank you again for your help... I'm sure I wouldn't have gotten this on my own!

Scott
Post Reply