MySQL Select x FROM y WHERE z="a", b="c", d="e";

SteelyDan · July 28, 2013

Hey -- I'm making a search engine for users to search my site, and I want them to only have to enter at least one of the four fields I have set out for them, in order to make a search. I think I have the code right to do that.

However, since it is AT LEAST 1 field, the other fields are set to wildcards. I want my script to

SELECT (5 different columns) FROM (a table called weapons) WHERE (their input = the values of the fields in each of the 5 columns).

Does that make sense?

Here is my code (A few lines down). I get an error saying:

Parse error: syntax error, unexpected '$buildname' (T_VARIABLE) in C:\web\search_query.php on line 39

Leading me to believe I have written my query wrong.

Apart from that, I want to generate a table when this search is conducted. Will my method work?

I basically echo the table start tag with headers,

Perform a loop which has the results in each new row,

Close the table header tag.

This seemed like the most logical way for me to do it.

Let me know if this is wrong or there is a better way to do it. But most importantly, I'd like help fixing this error and getting the proper MySQL SELECT>>>FROM>>>WHERE>>>a=$"var1", b=$"var2", c="var3";

<?php //submit_build.php

include_once 'header.php';
require_once 'login_builds.php';
include_once 'functions.php';

$db_server = mysql_connect($db_hostname, $db_username, $db_password);

mysql_select_db($db_database)
	or die("Unable to select database: " . mysql_error());

if (isset($_POST['buildname']) ||
	isset($_POST['weapon']) ||
	isset($_POST['category']) ||
	isset($_POST['id']))
	
{
if ($_POST['buildname'] == "") 
	{
		$buildname = "*";
	}
if ($_POST['weapon'] == "")
	{
		$weapon = "*";
	}
if ($_POST['category'] == "")
	{
		$category = "*";
	}
if ($_POST['id'] == "")
	{
		$id = "*";
	}
	

$buildname = sanitizeString($_POST['buildname']);

$searchstring = "SELECT buildname,weapon,category,id,author FROM weapons " .
				"WHERE buildname="$buildname", weapon="$weapon", category="$category", id="$id"";
$result = mysql_query($searchstring);

if (!$result) die ("Database access failed: " . mysql_error());

$rows = mysql_num_rows($result);

echo<<<_END

		<table class="fixed">
		<tr>
		<td class="newsbody">ID</td>
        <td class="newsbody">Build Name</td>
        <td class="newsbody">Weapon</td>
        <td class="newsbody">Category</td>
        <td class="newsbody">Author</td>
        </tr>
_END;

for ($j = 0; $j < $rows; ++$j)
{
echo<<<_END

        <tr>
        <td class="newsbody">$row[3]</td>
        <td class="newsbody"><a href="builds/$row[0].php">$row[0]</a></td>
        <td class="newsbody">$row[1]</td>
        <td class="newsbody">$row[2]</td>
        <td class="newsbody">$row[4]</td>
    	</tr>

_END;
}

echo<<<_END

		</table>
		
_END;

}

echo<<<_END

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

// FORM STARTS HERE, I JUST CHOSE TO HIDE IT BECAUSE IT IS LIKE 3000 LINES LONG

Hopefully I made myself clear.

Cheers!

Nick

Edited July 28, 2013 by SteelyDan

jazzman1 · July 28, 2013

Hey SteelyDan,

there is a beautiful fussion jazz band with the same name. I like them very much

Well, so far as I know that's not correct SQL statement especially after the "WHERE" clause.

$searchstring = "SELECT buildname,weapon,category,id,author FROM weapons " .
"WHERE buildname="$buildname", weapon="$weapon", category="$category", id="$id"";

Also, it contains a php syntax error.

Go to mysql' documentation and check how to use the sql where clause.

SteelyDan · July 28, 2013

Hey Jazzman1, I just saw SteelyDan last Sunday @ the Molson Amphitheatre in Toronto, haha.

But yeah, I guess what I'm asking is

a) Can I use the WHERE clause to include more than one value?

and if not...

b) How do I perform this function?

and

c) Would you mind pointing out my error? Lol

I have already looked at the WHERE clause in mySQL documentation, and it doesn't talk about using more than one variable.

Nick

jazzman1 · July 28, 2013

Good for you. I like people who listen jazz and rock.

So, try that:

$searchstring = "SELECT buildname,weapon,category,id,author FROM weapons WHERE buildname='$buildname' AND weapon='$weapon' AND category='$category' AND id=$id";

SteelyDan · July 28, 2013

Ok so I got the page to appear (figured out the parse error above). I can click submit, and it echos a little table in my document, but the table has no results in it. It only has the headers. Even when I modify the string to SELECT * FROM weapons; I get no results (and yes, the database has entries in it).

(SORRY JAZZMASTER, YOU POSTED LITERALLY 5 SECONDS BEFORE I DID, haha)

Here was the fix for the code above (or so I think. Maybe it is still the problem);

$searchstring = "SELECT buildname,weapon,category,id,author FROM weapons " .
		"WHERE buildname='$buildname' AND weapon='$weapon' AND category='$category' AND id='$id'";

Alterations include;

using AND instead of commas, and using single quotations around the variables.

Any help on getting my results to show?

Thanks,

Nick

I should also mention I realized I forgot to assign my variables from the $_POST array, so this was the code I added in.

$buildname 	= sanitizeString($_POST['buildname']);
$weapon 	= ($_POST['weapon']);
$category 	= ($_POST['category']);
$id 		= ($_POST['id']);

Edited July 28, 2013 by SteelyDan

boompa · July 29, 2013

Try printing the string you're sending to the database.

jazzman1 · July 29, 2013

Hey Nick,

few things I have to mention before to post my solution on that.

1. Your code looks a little bit messy. You need to separate the logic from the presentation, as you can see later.

2. Stop using all mysql_* functions, rather than start to learn the mysqli_* or pdo library.

3. Using too much AND....and....AND...and...AND after the "WHERE" clause points me out that there is something wrong in your database design.
I highly recommend, you to watch these 9 videos.

4. I don't know how you validate the values of the html form fields, but you should spend a time to learn more of that too.

So, try the code below and come back later on it, if you have any problems.

Regards,

jazz.

<?php

include_once 'header.php';

require_once 'login_builds.php';

include_once 'functions.php';

$db_server = mysql_connect($db_hostname, $db_username, $db_password);

mysql_select_db($db_database) or die("Unable to select database: " . mysql_error());

if (isset($_POST['buildname']) || isset($_POST['weapon']) || isset($_POST['category']) || isset($_POST['id'])) {
    if ($_POST['buildname'] == "") {
        $buildname = "*";
    }
    if ($_POST['weapon'] == "") {
        $weapon = "*";
    }
    if ($_POST['category'] == "") {
        $category = "*";
    }
    if ($_POST['id'] == "") {
        $id = "*";
    }
}

$buildname = sanitizeString($_POST['buildname']);

$searchstring = "SELECT buildname,weapon,category,id,author FROM weapons WHERE buildname='$buildname' AND weapon='$weapon' AND category='$category' AND id=$id";

$result = mysql_query($searchstring);

if (!$result) die("Database access failed: " . mysql_error());
// end the logic of your script here and start to display your presentation
?>

<table class="fixed" border="1">
    <tr>
        <td class="newsbody">ID</td>
        <td class="newsbody">Build Name</td>
        <td class="newsbody">Weapon</td>
        <td class="newsbody">Category</td>
        <td class="newsbody">Author</td>
    </tr>
    
    <?php if (mysql_num_rows($result) == 0) { // check if there are no records found in the database
        echo '<tr><td colspan="5">No records found</td></tr>';
    } else { while ($row = mysql_fetch_assoc($result)): // start table data layout ?>
    
            <tr>
                <td class="newsbody"><?php echo $row['id']; ?></td>
                <td class="newsbody"><a href="./builds/<?php echo $row['buildname'] ?>.php"><?php echo $row['buildname']; ?></a></td>
                <td class="newsbody"><?php echo $row['weapon'] ?></td>
                <td class="newsbody"><?php echo $row['category']; ?></td>
                <td class="newsbody"><?php echo $row['author']; ?></td>
            </tr>
    
            <?php endwhile; // end while loop and data layout
    } // end the else statement 

    echo "</table>\n"; // end the table

?>
    <html xmlns="http://www.w3.org/1999/xhtml">

        </body>

Zane · July 29, 2013

I would concat all the relevant fields into one string and use LIKE to match...

For instance

searchTerm = "something cheap and awesome"

SELECT itemID, itemName FROM items WHERE CONCAT('#', itemName, itemDesc, itemField, itemField2, itemField3) LIKE '%searchTerm%'

it also wouldn't hurt to replace all your spaces and other non-alphanum charaacters with a percent sign %.

Then replace duplicate % signs with one single % sign

This way your query will look like this

SELECT itemID, itemName FROM items WHERE CONCAT('#', itemName, itemDesc, itemField, itemField2, itemField3) LIKE '%something%cheap%and%awesome%'

The biggest drawback is that the longer the searchTerm is, the harder it will be to find a perfect match. You may have to reorganize your CONCAT to have the most relevant fields appear first...

Edited July 29, 2013 by Zane

Psycho · July 29, 2013

I was going to state something similar to Zane. Instead of having unique fields to search just by buildname or just by weapon, etc. you could instead have a single field for the user to enter their search values and compare against all the relevant columns. However, this is a business decision. It may actually result in a better, more user friendly experience. But, it could also result in some false positives. If the user needs explicit filtering (e.g. buildname is exactly a specific value and weapon is a specific value) then you probably need to stick with what you have.

However, as to why you weren't getting any results, doing an equal comparison to an asterisk is NOT a wildcard. It will look for matches that are exactly an asterisk. The asterisk is only a wild card when doing a LIKE comparison. Also, if you go with your previous logic, instead of doing a comparison of the wildcard - just remove the comparison completely.

Sign In

MySQL Select x FROM y WHERE z="a", b="c", d="e";

Recommended Posts

SteelyDan

Link to comment

Share on other sites

jazzman1

Link to comment

Share on other sites

SteelyDan

Link to comment

Share on other sites

jazzman1

Link to comment

Share on other sites

SteelyDan

Link to comment

Share on other sites

boompa

Link to comment

Share on other sites

jazzman1

Link to comment

Share on other sites

Zane

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information