There must be a simpler way MySQL query

[JasonDFR]

I am allowing users to search a mysql table. I want the user's search term to return rows that contain the search term in any of 5 different columns (city, departnum, depart, region, postal). I have made 5 different SELECT statements, moving to the next if a result isn't found. The following code works, but there must be a simpler way, I just can not figure it out.

BTW, I am teaching myself php and mysql. I am new. Thanks for the help!

$query = "SELECT * FROM marches WHERE city = \"$search\" ORDER BY city";

// Results
$numberresults=mysql_query($query);
$numberrows=mysql_num_rows($nresults);

if ($numrows == 0)
{
$query = "SELECT * FROM marches WHERE departnum = \"$trimmed\" ORDER BY city";
}

// Results
$numberresults=mysql_query($query);
$numberrows=mysql_num_rows($nresults);

if ($numrows == 0)
{
$query = "SELECT * FROM marches WHERE depart = \"$trimmed\" ORDER BY city";
}

// Results
$numberresults=mysql_query($query);
$numberrows=mysql_num_rows($nresults);

if ($numrows == 0)
{
$query = "SELECT * FROM marches WHERE region = \"$trimmed\" ORDER BY city";
}

// Results
$numberresults=mysql_query($query);
$numberrows=mysql_num_rows($nresults);

if ($numrows == 0)
{
$query = "SELECT * FROM marches WHERE postal = \"$trimmed\" ORDER BY city";
}

// Results
$numberresults=mysql_query($query);
$numberrows=mysql_num_rows($nresults);

?>

Thanks so much. As always, these forums are great. I have learned so much from them already.

Jason

[Nile]

There isn't a simpler way from my knowledge unless you create a function.
Not that much simpler.

function qur($query){
mysql_query("SELECT * ".$query);
}
$query = "FROM marches WHERE city = \"$search\" ORDER BY city";
// Results
$numberresults=qur($query);
$numberrows=mysql_num_rows($nresults);
if ($numrows == 0)
{
$query = "FROM marches WHERE departnum = \"$trimmed\" ORDER BY city";
}
// Results
$numberresults=qur$query);
$numberrows=mysql_num_rows($nresults);
if ($numrows == 0)
{
$query = " FROM marches WHERE depart = \"$trimmed\" ORDER BY city";
}
// Results
$numberresults=qur($query);
$numberrows=mysql_num_rows($nresults);
if ($numrows == 0)
{
$query = "FROM marches WHERE region = \"$trimmed\" ORDER BY city";
}
// Results
$numberresults=qur($query);
$numberrows=mysql_num_rows($nresults);
if ($numrows == 0)
{
$query = "FROM marches WHERE postal = \"$trimmed\" ORDER BY city";
}
// Results
$numberresults=qur($query);
$numberrows=mysql_num_rows($nresults);
?>

And then you can use the switch() function for the ifs, or you can just put everything inside one if, because its all the same if($num == 0).

[JasonDFR]

Thanks Nile!

So, are you saying that what I posted is CORRECT?

If so, I just impressed myself.

Thanks a lot,

J

[Nile]

It is very correct, except in the future(this is an example):


SELECT * FROM donuts WHERE glazed="true"

It should really be this:


SELECT * FROM `donuts` WHERE `glazed`="true"

[phpsales]

If you are looking to shorten the query/code:


$sql = sprintf("SELECT * FROM `marches` WHERE
`city` = '%s' or `departnum` = '%s'
or `depart` = '%s' or `region` = '%s'
or `postal` = '%s' ORDER BY `city` asc",
mysql_real_escape_string($search),
mysql_real_escape_string($search),
mysql_real_escape_string($search),
mysql_real_escape_string($search),
mysql_real_escape_string($search));

$result = mysql_query($sql) or die(mysql_error());
$rows = mysql_num_rows($result);

[JasonDFR]

How do you use LIKE in the sprintf function above? I would rather use LIKE instead of WHERE so a search for "Pars" would return "Paris."

I tried using "\%" in front of "%s" , but this doesn't work......

Thanks again for the help.

[boogyman]

sprintf is a function in php that allows you to separate the string you want to output and the variables you are using to do so....


sprintf("STRING %s STRING %i ", $string, $integer);

for more information regarding the function, review the php manual (http://www.php.net)

now as for the MySQL "LIKE" syntax... the percent sign represents a wildcard character, meaning that it can be any single/multiple character(s)


SELECT city, country, region FROM places WHERE city LIKE 'Par%s'

Checking for values is case-sensitive, meaning if the city is Paris, and you check paris, your query will return no results... MySQL has made a couple of other useful functions to compat this issue.... similar to php's strtolower()...


SELECT city, country, region FROM places WHERE lower(city) LIKE 'par%s'

Now that query will match any city starting with the characters par in any case (UPPER / lower) ... have any single/multiple additional characters, and end in either a CAPITAL S or a lowercase s