Page 1 of 1
Newbie needs help - code returning empty results
Posted: Wed Jun 15, 2005 4:46 pm
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
Posted: Wed Jun 15, 2005 4:50 pm
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

Posted: Wed Jun 15, 2005 5:14 pm
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 -

)
So, I've made some changes:
Code: Select all
$result = mysql_query("e;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"e;,$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!

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
Posted: Wed Jun 15, 2005 5:24 pm
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.
Posted: Wed Jun 15, 2005 5:50 pm
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
Posted: Wed Jun 15, 2005 8:02 pm
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!
Thank you again for your help... I'm sure I wouldn't have gotten this on my own!
Scott