mysql query returns error

andyw

When I run this query from the command line it works great:
SELECT COUNT(surid) AS Totalsurid, COUNT(age) AS Adultcount FROM survey WHERE ruid like 11 and age like "adult";
and returns what I ask for, Totalsurid 7,Adultcount 7.

When I enter this in php and request it from the browser, it returns: Unexpected string on line 5, line 5 being the $sql.

<?php
$conn=mysql_connect("localhost");
mysql_select_db("survey", $conn);

$sql="SELECT COUNT(surid) AS Totalsurid, COUNT(age) AS Adultcount FROM survey WHERE ruid like 11 and age like "adult";
$result=mysql_query($sql, $conn);
if($result)
{
echo "Result is OK";
}
else
{
echo "No Result";
}
exit;

What change is needed?

Thanks, Andy

olaf

use inside the mysql statement single quotes:

$sql="SELECT COUNT(surid) AS Totalsurid, COUNT(age) AS Adultcount FROM survey WHERE ruid like 11 and age like 'adult'"

andyw

Thank you, Olaf. I am now past the error message.

I attempted to use the list function to assign variables to the query results but was not successful. Could you suggest an appropriate method of having variables assigned to the query results so that I can call them into a report?

<?php
$conn=mysql_connect("localhost");
mysql_select_db("survey", $conn);
$sql="SELECT COUNT(surid) AS Totalsurid, COUNT(age) AS Adultcount FROM survey WHERE ruid like 11 and age like 'adult'";
$result=mysql_query($sql, $conn);
if($result)
{
echo "Result is OK<hr width=20%>";
}
else
{
echo "No Result";
}
list($Totalcount, $Adultcount)=$result;
echo "Totalcount : $Totalcount<hr width=30%>";
echo "Adult count : $Adultcount<hr width=50%>";
?>

tsinka

Hi,

where does $Totalcount come from, I only see Totalsurid and Totalcount in the query ?

Besides that ... what exactly do you want to select ? I think the query can be optimized.

And you need to use e.g. mysql_fetch_row to get the row data like

$result=@mysql_query($sql, $conn); 
if (!$result) {
  echo "There was a SQL error: ".mysql_error()."<hr width=20%>"; 
} else {
  $row = mysql_fetch_array($result);  

  $Totalcount = $row['Totalsurid'];
  $Adultcount = $row['Adultcount'];
  echo "Totalcount : $Totalcount<hr width=30%>"; 
  echo "Adult count : $Adultcount<hr width=50%>"; 
}

Thomas

andyw

My table has 15 fields I must total. 11 fields are checkbox responses, I need only to total the the responses for each of these fields. Three fields are radio fields I must only total the first response for each field. One field is a select with 4 options, and I must total each option. At present I have 19 select statements which do produce the correct output but the total length of the file is 102 lines. When I am able to determine the php syntax that will give me $variables that I can use for additional formulas, my sql query will be long. I appreciate your time. Thanks, Andy

tsinka

It might help if you post the post (attached as a zip to your post because it is long).
I need to know a little bit more about the HTML form. How does it look like and how is the data of the select stored in the table ?

I'm pretty sure that the number of queries can be reduced to a minimum but I need to know more about the application/table structure.

Thomas

andyw

The file includes the imput form 'survey.html', 'survey.sql', and 'postinfo.php'.
The file 'graph.php' reflects how I wish to present the information gathered from four questions, [gneq], [qualq], [effectq], and [accessq]. Although the presentation is OK, the composition of the queries is poor. The file 'count.php' is the current work in process.
Your help is genuinely appreciated. When this is ready to go, I will dump aprx 1000 records from an ms access database into this mysql db. Thanks again, Andy

tsinka

Hi,

still don't know exactly how the script looks like but if you want to get the different counts you might use GROUP BY.

Something like e.g.:

SELECT count(*) AS cnt FROM survey WHERE ruid=11 GROUP BY age,field2,field3,field4

That would return four records each containing the count for one of those fields. Might need some tweaking but should go somehow in the right direction.

Give me some real world example (example rows in the table and how they should be counted)

Thomas

andyw

Thomas, I am again attaching a zip file which shows examples. Hope it comes through this time. Andy

pnoeric

Andy - FWIW one good trick when developing SQL stuff is to build your queries in an interactive tool (the best PHP-native one is phpMyAdmin, google for a download page)... once you get exactly the data you want when you do the SQL calls in there, then you can put them into your PHP code. make sense? it's usually faster and more interactive than fooling around with the PHP code at the same time as you're trying to figure out what data you want to get.

(which reminds me of one more cardinal rule of debugging that I didn't include below: work on one bug at a time! when you're trying to fix two things at once, that might be related, it can make you crazy...)

best
Eric

andyw

Thank you Eric. Your thoughts are right on the money. I am able to extract the correct information from mysql using the dos prompt. I am able to extract the correct information with php using (far to numerous) multiple queries. I have not yet figured out how to translate the query that works in dos, to the php file and use the data. ie: $sql="SELECT COUNT(surid) AS Totalsurid, COUNT(age) AS Adultcount FROM survey WHERE ruid like 11 and age like 'adult'".This format works in php to return the correct information. I need to define each returned answer as a variable that I can work with later in the script. How do I pick up Totalsurid and Adultcount as variables? I attempted to use the list() function with ($result) from the statement $result=mysql_query($sql, $conn) as the argument; failed.
Your input is welcome. Thanks, Andy

tsinka

Hi Andy,

did you try the code I posted (with the mysql_fetch_array) ?

Thomas

andyw

Thomas,

You are correct! I did run a test with the code you suggested initially and it failed because of a dumb error on my part.

I have corrected my error and your example IS the answer.

I note also, your example of using "mysql_error()" to better advantage.

I now can proceed to clean up the file.

Thanks for all your help.

andyw

Things are going well. The input form, post form and report for are performing as expected. The problem now is uploading the files from the old database ot the new. Because the addess db is comprised of 7 separate but linked tables and the mysql db has but two tables, I converted the access db to an excell spreadsheet where all the 7 tables are listed, side by side. I changed the column headings in the excell file to match exactly, the column headings in the mysqldb. The script I found is from a host provider giving instructions to his subscribers and hope it will work for me.

<?php
$conn=mysql_connect("localhost");
$rs=mysql_select_db("survey", $conn);
$fcontents=file('./cfst.xls');
if($fcontents)
{
echo "Is Contents";
}
else
{
echo "No contects";
}
for($i=0; $i<sizeof($fcontents); $i++)
{
$line=trim($fcontents[$i]);
$arr=explode("\t", $line);
$sql="INSERT INTO survey VALUES ('".implode("','",$arr).'")";
$rs=mysql_query($sql, $conn);
echo "$rs.<br>\n";
}
if($rs)
{
echo "Is OK sql";
}
else
{
echo "Is no sql";
}

However, all it returns is "error: unexpected end of file at line XX.

Any suggestions how to make this work?

tsinka

Hi,

change this

$sql="INSERT INTO survey VALUES ('".implode("','",$arr).'")";

$sql="INSERT INTO survey VALUES ('".implode("','",$arr)."')";

Thomas

andyw

Thomas, we are getting close. I copy & pasted your post, ran the php and it returned "Is Contents" and "Is no sql". These two comments are separated by a huge blank space. Since the file 'cfst.xls' has 617 rows, it appears we are darn close. Checking the table from the dos prompt indicates the new information has not been added. I changed the "\t" to "\," and the results were the same.

Good try, though. Thanks, Andy

tsinka

Hi,

please post one line of the file.

Thomas

andyw

Thomas, when I copy the entire page that is returned to a text file, the file size is 3kb. The first lines follow:
Is Contents

This should be the end of whatever exists in that file.

I am totally lost.

Andy

tsinka

Pease post the code.

andyw

Thomas, I have been successful in converting my msaccess tables in to excel, then to csv and finally, into mysql from the command prompt load data local file.

This thread is complete. Your patience and you help have been crucial. Thank you.