Jump to content


Photo

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


  • Please log in to reply
8 replies to this topic

#1 SteelyDan

SteelyDan

    Newbie

  • New Members
  • Pip
  • 8 posts

Posted 28 July 2013 - 01:49 PM

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 by SteelyDan, 28 July 2013 - 01:49 PM.


#2 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,384 posts
  • LocationMississauga, Canada

Posted 28 July 2013 - 02:07 PM

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.



#3 SteelyDan

SteelyDan

    Newbie

  • New Members
  • Pip
  • 8 posts

Posted 28 July 2013 - 02:12 PM

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



#4 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,384 posts
  • LocationMississauga, Canada

Posted 28 July 2013 - 02:42 PM

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";


#5 SteelyDan

SteelyDan

    Newbie

  • New Members
  • Pip
  • 8 posts

Posted 28 July 2013 - 02:43 PM

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 by SteelyDan, 28 July 2013 - 02:46 PM.


#6 boompa

boompa

    Advanced Member

  • Members
  • PipPipPip
  • 236 posts
  • LocationMassachusetts

Posted 29 July 2013 - 08:34 AM

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



#7 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,384 posts
  • LocationMississauga, Canada

Posted 29 July 2013 - 12:19 PM

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>


#8 Zane

Zane

    Advanced Member

  • Moderators
  • 3,819 posts
  • LocationMurphy, NC

Posted 29 July 2013 - 12:52 PM

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 by Zane, 29 July 2013 - 12:54 PM.

btn_donate_SM.gif  Want to thank me?  Contribute to my PayPal piggy-bank
 
401299.png

#9 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,709 posts
  • LocationCanada

Posted 29 July 2013 - 01:23 PM

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.


The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com