Jump to content


Photo

MySQL Query Output Problems


  • Please log in to reply
10 replies to this topic

#1 Sallie_ann

Sallie_ann
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 09 July 2003 - 09:18 AM

Hi...

I have connected to a MySQL database that contains information on various companies. I have created a HTML form with 4 drop down list boxes that have static information to (hopefully) search the database including company size, location, sector, etc. The user will select items from the drop down list boxes and click go to retrieve the required data.

For example, a user may want to search for all medium sized companies in Leicester in the IT sector. The PHP code I have so far is as follows;


mysql_select_db(\"beacon\",$db);

if($_POST[\'company_size\'] == 1)
$where = \'company_size BETWEEN 1 AND 9\';
if($_POST[\'company_size\'] == 2)
$where = \'company_size BETWEEN 10 AND 49\';
if($_POST[\'company_size\'] == 3)
$where = \'company_size BETWEEN 50 AND 250\';

if($_POST[\'County\'] == 1)
$where1 = \'County LIKE Derbyshire\';
if($_POST[\'County\'] == 2)
$where1 = \'County LIKE Leicestershire\';
if($_POST[\'County\'] == 3)
$where1 = \'County LIKE Lincolnshire\';
if($_POST[\'County\'] == 4)
$where1 = \'County LIKE Northants\';
if($_POST[\'County\'] == 5)
$where1 = \'County LIKE Nottinghamshire\';

$result = mysql_query(\"SELECT name, County, sector, company_size \" .
\"FROM beacon \" .
\"WHERE\". $where .\"AND\". $where1);

echo \"<table border=1 align=center>n\";

echo \"<tr><td>Name</td><td>County</td><td>Sector</td><td>Company Size</td></tr>n\";

while ($myrow = mysql_query($query))

{

printf(\"<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>n\",
$myrow[0], $myrow[1], $myrow[2], $myrow[3]);

}

echo \"</table>n\";


An example of my HTML drop down list code is as follows;


<select name=\"company_size\">
<option value=\"0\" selected>Select Company Size</option>
<option value=\"1\">1 - 9</option>
<option value=\"2\">10 - 49</option>
<option value=\"3\">50 - 250</option>
</select>



No data is being output from the database except the table column headings. I managed to output all the database data in the table before I entered the query so I know the problem lies here.

Any help would be really appreciated.

Sallie-ann

#2 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 09 July 2003 - 09:29 AM

The problme is in query

ur query should be like

$where = \'company_size BETWEEN 1 AND 9\';

$where1 = \'County LIKE \'Leicestershire%\' \';

$result = mysql_query(\"SELECT name, County, sector, company_size
FROM beacon WHERE. $where .\"AND\". $where1) or die(\"Problem\");


Remember ur LIKE synatx

should be this way


select * from table where name LIKE \'php\'

or

select * from table where name LIKE \'%php\'


use the die statement to see if ur query is working properly
Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#3 Sallie_ann

Sallie_ann
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 09 July 2003 - 09:50 AM

Thanks for your help, my queries now read as follows;



mysql_select_db(\"beacon\",$db);

if($_POST[\'company_size\'] == 1)
$where = \'company_size BETWEEN 1 AND 9\';
if($_POST[\'company_size\'] == 2)
$where = \'company_size BETWEEN 10 AND 49\';
if($_POST[\'company_size\'] == 3)
$where = \'company_size BETWEEN 50 AND 250\';

if($_POST[\'County\'] == 1)
$where1 = \'County LIKE \'Derbyshire%\' \';
if($_POST[\'County\'] == 2)
$where1 = \'County LIKE \'Leicestershire%\' \';
if($_POST[\'County\'] == 3)
$where1 = \'County LIKE \'Lincolnshire%\' \';
if($_POST[\'County\'] == 4)
$where1 = \'County LIKE \'Northants%\' \';
if($_POST[\'County\'] == 5)
$where1 = \'County LIKE \'Nottinghamshire%\' \';

$result = mysql_query(\"SELECT name, County, sector, company_size
FROM beacon WHERE. $where .\"AND\". $where1) or die(\"Problem\");

echo \"<table border=1 align=center>n\";

echo \"<tr><td>Name</td><td>County</td><td>Sector</td><td>Company Size</td></tr>n\";

while ($myrow = mysql_query($query))

{

printf(\"<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>n\",
$myrow[0], $myrow[1], $myrow[2], $myrow[3]);

}

echo \"</table>n\";



I am being given the following error message referring to the first echo.

\"Parse error: parse error, unexpected T_STRING\"

And I\'m unsure why!

#4 Sallie_ann

Sallie_ann
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 09 July 2003 - 09:53 AM

And I am now also getting error messages for the first line of my query

$where1 = \'County LIKE \'Derbyshire%\' \';

:oops:

#5 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,667 posts
  • LocationLos Angeles, CA USA

Posted 09 July 2003 - 10:29 AM

And I am now also getting error messages for the first line of my query

$where1 = \'County LIKE \'Derbyshire%\' \';  

:oops:


It\'s your quotes. Switch to

$where1 = \"County LIKE \'Derbyshire%\'\";

#6 Sallie_ann

Sallie_ann
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 09 July 2003 - 10:43 AM

I have changed my WHERE statements as you suggested and am now being given error messages for the final line in the following extract of code. :?


if($_POST[\'County\'] == 1)
$where1 = \"County LIKE \'Derbyshire%\'\";
if($_POST[\'County\'] == 2)
$where1 = \"County LIKE \'Leicestershire%\'\";
if($_POST[\'County\'] == 3)
$where1 = \"County LIKE \'Lincolnshire%\'\";
if($_POST[\'County\'] == 4)
$where1 = \"County LIKE \'Northants%\'\";
if($_POST[\'County\'] == 5)
$where1 = \"County LIKE \'Nottinghamshire%\'\";

$result = mysql_query(\"SELECT name, County, sector, company_size
FROM beacon WHERE. $where .\"AND\". $where1) or die(\"Problem\");

#7 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 09 July 2003 - 10:55 AM

Try this

$result = mysql_query("SELECT name, County, sector, company_size FROM beacon WHERE". $where ."AND". $where1) or die("Problem");

Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#8 Sallie_ann

Sallie_ann
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 09 July 2003 - 12:17 PM

It is now outputting \"Problem\". My complete code is now as follows;


mysql_select_db(\"beacon\",$db);

if($_POST[\'company_size\'] == 1)
$where = \'company_size BETWEEN 1 AND 9\';
if($_POST[\'company_size\'] == 2)
$where = \'company_size BETWEEN 10 AND 49\';
if($_POST[\'company_size\'] == 3)
$where = \'company_size BETWEEN 50 AND 250\';

if($_POST[\'County\'] == 1)
$where1 = \"County LIKE \'Derbyshire%\'\";
if($_POST[\'County\'] == 2)
$where1 = \"County LIKE \'Leicestershire%\'\";
if($_POST[\'County\'] == 3)
$where1 = \"County LIKE \'Lincolnshire%\'\";
if($_POST[\'County\'] == 4)
$where1 = \"County LIKE \'Northants%\'\";
if($_POST[\'County\'] == 5)
$where1 = \"County LIKE \'Nottinghamshire%\'\";

$result = mysql_query(\"SELECT name, County, sector, company_size
FROM beacon WHERE\". $where .\"AND\". $where1) or die(\"Problem\");

echo \"<table border=1 align=center>n\";

echo \"<tr><td>Name</td><td>County</td><td>Sector</td><td>Company Size</td></tr>n\";

while ($myrow = mysql_query($query))

{

printf(\"<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>n\",
$myrow[0], $myrow[1], $myrow[2], $myrow[3]);

}

echo \"</table>n\";

#9 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 09 July 2003 - 12:40 PM

As a suggestion

Try this

echo $sql which will be the query now execute the query separtely and see if u are getting the desired output.

$sql = "SELECT name, County, sector, company_size FROM beacon WHERE". $where ."AND". $where1;echo $sql;$result = mysql_query($sql) or die("Problem");

Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#10 Sallie_ann

Sallie_ann
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 09 July 2003 - 01:15 PM

It is now outputting;


SELECT name, County, sector, company_size FROM beacon WHEREcompany_size BETWEEN 1 AND 9ANDProblem

#11 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 09 July 2003 - 02:04 PM

I guess what is being displayed is not the complete query is that so?

SELECT name, County, sector, company_size FROM beacon WHEREcompany_size BETWEEN 1 AND 9 AND County LIKE \'Leicestershire%\'

See that u send the correct value and alos I see there is no space

\"BETWEEN 1 AND 9AND\" so u have to add a space inbetween
Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users