Selecting from a Database

mainelydesign · September 13, 2009

I am creating a search function to call from my DB to display certain information. I am using a series of drop downs atm and need to be able to have an All option to call every entry in that field.

Here is the code as it stands:

<?
include 'connection.php';
$dog = "dog_breed";
$breed = $_POST['slctbreed'];

//build and issue query
$sql = "SELECT count(id) FROM $table_name";
$result = @mysql_query($sql, $connection) or die(mysql_error());
$count = @mysql_result($result, 0, "count(id)") or die(mysql_error());

//get birthday count
$get_dog_count = "SELECT count(id) FROM $table_name WHERE $dog = '$breed'";
$dog_count_res = @mysql_query($get_dog_count, $connection) or die(mysql_error());
$dog_count = mysql_result($dog_count_res, 0, "count(id)");

//create a list,based on a postive result
if ($dog_count > 0) {
    $dog_string = "<ul>";
    $get_dogs = "SELECT id, bname, dog_breed FROM $table_name WHERE $dog = '$breed' ORDER BY bname";
    $dog_res = @mysql_query($get_dogs, $connection) or die(mysql_error());
    while ($doggy = mysql_fetch_array($dog_res)) {
        $dog_id = $doggy['id'];
        $dog_bname = $doggy['bname'];
        $dog_brd = $doggy['dog_breed'];
        $dog_string .= "<li><a href=\"show_breed.php?id=$dog_id\">$dog_bname $dog_brd</a>";
    }
    
    $dog_string .= "</ul>";
}

//build menu block
$display_block = "
<P><strong>Miscellaneous</strong></P>
<ul>
<li>All breeds in DB:<strong>$count</strong>
<li>$breed types:<strong>$dog_count</strong>
$dog_string
</ul>";



?>
<HTML>
    <HEAD>
        <TITLE>Dog Breeds</TITLE>
    </HEAD>
    <BODY>
        <? echo "$display_block"; ?>
        <p>
            <form action="<?php echo $_SERVER['PHP_SELF']; ?>" name="submit" method="post">
                <select name="breed" size="1">
                	<option value="all">all</option>
                    <option value="pitbull">Pitbull</option>
                    <option value="boxer">Boxer</option>
                    <option value="shitzu">Shitzu</option>
                </select>
                <input type="submit" name="Submit"value="Submit">
            </form>
        </p>
    </BODY>
</HTML>

Where I am having difficulty is determining a value for when ALL is selected to be placed in the WHERE string so that is $dog which is my field in the DB is equal to get everything in that field. I tried using a if then and it wasn't close to working. Any suggestions would be appreciated.

Thanks in advance.

cbolson · September 13, 2009

Hi,

I would condition your query so that, if $breed=="all" you don't include the WHERE clause:

eg.

$get_dog_count = "SELECT count(id) FROM $table_name";
if($breed!="all") $get_dog_count.=" WHERE $dog = '$breed'";

Chris

mainelydesign · September 13, 2009

Thanks Chris I am trying that now, appreciate the help.

mainelydesign · September 13, 2009

I have tried making the query conditional, however I am getting a syntax error revolving aroundthe use of $dog = '$breed'.

Here is the revised code:

$get_dog_count = "SELECT count(id) FROM $table_name";
if($breed!="all") $get_dog_count.=" WHERE $dog = '$breed'";
$dog_count_res = @mysql_query($get_dog_count, $connection) or die(mysql_error());
$dog_count = mysql_result($dog_count_res, 0, "count(id)");

//create a list,based on a postive result
if ($dog_count > 0) {
    $dog_string = "<ul>";
    $get_dogs = "SELECT id, bname, dog_breed FROM $table_name ORDER BY bname";
if($breed!="all") $get_dogs.=" WHERE $dog = '$breed' ORDER BY bname";
    $dog_res = @mysql_query($get_dogs, $connection) or die(mysql_error());
    while ($doggy = mysql_fetch_array($dog_res)) {
        $dog_id = $doggy['id'];
        $dog_bname = $doggy['bname'];
        $dog_brd = $doggy['dog_breed'];
        $dog_string .= "<li><a href=\"show_breed.php?id=$dog_id\">$dog_bname $dog_brd</a>";
    }
    
    $dog_string .= "</ul>";
}

yettti · September 13, 2009

.. Careful, theres no protection against SQL injection in there !

remember to use

addslashes

and

stripslashes

where necessary!

(you probably already know, but just in case...)

mainelydesign · September 13, 2009

Thanks, was just reading up on SQL injections as i am a novice. Thanks for the heads up, After i get this working (on my local machine) i will start focusing on proper protection form SQL Injection.

Again Thanks

cbolson · September 13, 2009

The ORDER BY must go after the WHILE condition.

You have this:

$get_dogs = "SELECT id, bname, dog_breed FROM $table_name ORDER BY bname";
   if($breed!="all") $get_dogs.=" WHERE $dog = '$breed' ORDER BY bname";

That should be:

$get_dogs = "SELECT id, bname, dog_breed FROM $table_name ";
if($breed!="all") $get_dogs.=" WHERE $dog = '$breed'";
$get_dogs.="ORDER BY bname";

Chris

mainelydesign · September 13, 2009

That makes sense now that I look at. Still getting the syntax error from the = in the WHERE string.

if($breed!="all") $get_dog_count.=" WHERE $dog = '$breed'";

Now when i use the could in its original stated before adding the all option and condition then the script runs and searches approriately based on the selection form the drop down. Not sure why the = is an issue now as the error message doesnt suggest more then a syntax error.

cbolson · September 13, 2009

I can't see the error in the snippet that you sent, but bear in mind that pho errors are often actually in the line before the line that is indicated in the error message.

Also, you should also check that $breeds has a value.

Something like:

if( ($breeds!="") AND ($breeds!="all") ) $sql.=" WHERE $dog='".$breed."'";

.... I have just realisd you have $dog='$breed'

Where is the variable $dog set?

Should this not just be "dog" (ie no $) ?

(I should have spotted that before ::) )

Chris

mainelydesign · September 13, 2009

Here is the Full Code

<?
include 'connection.php';
$dog = "dog_breed";
$breed = $_POST['slctbreed'];

//build and issue query
$sql = "SELECT count(id) FROM $table_name";
$result = @mysql_query($sql, $connection) or die(mysql_error());
$count = @mysql_result($result, 0, "count(id)") or die(mysql_error());



//get dog count
$get_dog_count = "SELECT count(id) FROM $table_name";
if($breed!="all") $get_dog_count.=" WHERE $dog = '$breed'";
$dog_count_res = @mysql_query($get_dog_count, $connection) or die(mysql_error());
$dog_count = mysql_result($dog_count_res, 0, "count(id)");

//create a list,based on a postive result
if ($dog_count > 0) {
    $dog_string = "<ul>";
    $get_dogs = "SELECT id, bname, dog_breed FROM $table_name ORDER BY bname";
if($breed!="all") $get_dogs.=" WHERE $dog = '$breed' ORDER BY bname";
    $dog_res = @mysql_query($get_dogs, $connection) or die(mysql_error());
    while ($doggy = mysql_fetch_array($dog_res)) {
        $dog_id = $doggy['id'];
        $dog_bname = $doggy['bname'];
        $dog_brd = $doggy['dog_breed'];
        $dog_string .= "<li><a href=\"show_breed.php?id=$dog_id\">$dog_bname $dog_brd</a>";
    }
    
    $dog_string .= "</ul>";
}

//build menu block
$display_block = "
<P><strong>Miscellaneous</strong></P>
<ul>
<li>All breeds in DB:<strong>$count</strong>
<li>$breed types:<strong>$dog_count</strong>
$dog_string
</ul>";



?>
<HTML>
    <HEAD>
        <TITLE>Dog Breeds</TITLE>
    </HEAD>
    <BODY>
        <? echo "$display_block"; ?>
        <p>
            <form action="<?php echo $_SERVER['PHP_SELF']; ?>" name="submit" method="post">
                <select name="breed" size="1">
                	<option value="all">all</option>
                    <option value="pitbull">Pitbull</option>
                    <option value="boxer">Boxer</option>
                    <option value="shitzu">Shitzu</option>
                </select>
                <input type="submit" name="Submit"value="Submit">
            </form>
        </p>
    </BODY>
</HTML>

Basically I am using a drop down menu in a for to identify the breed value and am using a set variable for the field. I probably should just put the field in the string instead of a variable that says what it is.

mainelydesign · September 13, 2009

This is cleaned up a little:

<?
include 'connection.php';

$breed = $_POST['slctbreed'];

//build and issue query
$sql = "SELECT count(id) FROM $table_name";
$result = @mysql_query($sql, $connection) or die(mysql_error());
$count = @mysql_result($result, 0, "count(id)") or die(mysql_error());



//get birthday count
$get_dog_count = "SELECT count(id) FROM $table_name";
if($breed!="all") $get_dog_count.=" WHERE dog_breed = '$breed'";
$dog_count_res = @mysql_query($get_dog_count, $connection) or die(mysql_error());
$dog_count = mysql_result($dog_count_res, 0, "count(id)");

//create a list,based on a postive result
if ($dog_count > 0) {
    $dog_string = "<ul>";
    $get_dogs = "SELECT id, bname, dog_breed FROM $table_name";
if($breed!="all") $get_dogs.=" WHERE dog_breed = '$breed'";
$get_dogs.="ORDER BY bname";
    $dog_res = @mysql_query($get_dogs, $connection) or die(mysql_error());
    while ($doggy = mysql_fetch_array($dog_res)) {
        $dog_id = $doggy['id'];
        $dog_bname = $doggy['bname'];
        $dog_brd = $doggy['dog_breed'];
        $dog_string .= "<li><a href=\"show_breed.php?id=$dog_id\">$dog_bname $dog_brd</a>";
    }
    
    $dog_string .= "</ul>";
}

//build menu block
$display_block = "
<P><strong>Miscellaneous</strong></P>
<ul>
<li>All breeds in DB:<strong>$count</strong>
<li>$breed types:<strong>$dog_count</strong>
$dog_string
</ul>";



?>
<HTML>
    <HEAD>
        <TITLE>Dog Breeds</TITLE>
    </HEAD>
    <BODY>
        <? echo "$display_block"; ?>
        <p>
            <form action="<?php echo $_SERVER['PHP_SELF']; ?>" name="submit" method="post">
                <select name="slctbreed" size="1">
                	<option value="all">all</option>
                    <option value="pitbull">Pitbull</option>
                    <option value="boxer">Boxer</option>
                    <option value="shitzu">Shitzu</option>
                </select>
                <input type="submit" name="Submit"value="Submit">
            </form>
        </p>
    </BODY>
</HTML>

Now I am setting the field in the WHERE string rather then in a variable.

cbolson · September 13, 2009

and is it working now?

mainelydesign · September 13, 2009

No it is still having issues LOL.

I keep getting this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=' at line 1

Not sure why it does not like the equals. I am viewing it out of Aptana atm, might that be an issue?

And again thanks for the assistance Chris

cbolson · September 13, 2009

line 1

Doesn't look like a line from the code that you have pasted.....

Is the error actually in connection.php???

Otherwise, have you echo'ed your query to see what is actually being used?

$get_dog_count = "SELECT count(id) FROM $table_name";
if($breed!="all") $get_dog_count.=" WHERE dog_breed = '$breed'";
echo $query;

Chris

mainelydesign · September 13, 2009

The connection is fine, tested that already. I have tried echoing the $sql down but the syntax error is preventing it. Will post back after some exxperimenting.

mainelydesign · September 13, 2009

I have worked out a rough solution but am having trouble tying the last piece of the idea together.

here it is:

$dogbrd = $_POST['breed'];


if ($dogbrd =="ALL"){
$dog = 'pitbull, boxer, shitzu';
}else{
$dog = $_POST['breed'];
}
$get_contacts_in = "SELECT id, bname, dog_breed FROM $table_name WHERE dog_breed = '$dog' ORDER BY bname";

What i need to be able to do is have multiple breed types in the all variable so that dog_breed = pitbull and boxer and shitzu thus pulling all the field entries and displaying them. I tried the * as SELECT * FROM $table_name pulls everything in the DB but it does not work right (prob a dumb attempt LOL). is there a command that can be used like dog_breed = ALL?

Thanks

cbolson · September 13, 2009

I tried the * as SELECT * FROM $table_name pulls everything in the DB but it does not work right

What do you mean by "it does not work right"

If you want all the dogs regardless of breed from the database, there is no need to have a WHERE clause.

Chris

mainelydesign · September 13, 2009

Sorry for the confusion. SEECT * FROM $table_name works fine without the where statement. However what i meant to state (more clearly) was the WHERE dog_breed = '*' does not work as a method to grab all entries in that field.

My end goal is to have a series of drop down select fields allowing users to select an option or all. if all is selected then the query needs to pull all of the entries in the specific field.

Using the WHERE function as an .= addition for the earlier suggestions, did not produce a result other then a syntax error each time. So I have moved backward a little to an if else variable but am unable to declare ALL so that it grabs all of the entries.

cbolson · September 13, 2009

The thing is that if you want all the entries you don't need/want any WHERE clause.

Can you post your php code as you have it now?

Also, it might be useful to see your code (form) that posts the users choice

Chris

Sign In

Selecting from a Database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information