Searching DB Problem

StevenF · 02-27-2008, 11:54 PM

I have another small problem :( I'll start off by showing the code I have so far:

PHP Code:


			
                        <tr>
                        <td>Artist:</td>
                        <td><input type="text" name="cd_artist" value="" /></td>
                        </tr>
                        
                        <tr>
                        <td>Title:</td>
                        <td><input type="text" name="cd_title" value="" /></td>
                        </tr>
                        
                        <tr>
                        <td>Price:</td>
                        <td><input type="text" name="cd_price" value="£" /></td>
                        </tr>
                        
                        <tr>
                        <td>Image No:</td>
                        <td><input type="text" name="cd_image_name" value="" /></td>
                        </tr>
                    
                        <tr>
                        <td>&nbsp;</td>
                        <td><input type="submit" value="Save" name="submit" /></td>

PHP Code:


			
//setting variables to grab data from the form
        $search_artist = mysql_real_escape_string($_POST['search_artist']);
        $search_title = mysql_real_escape_string($_POST['search_title']);
        
        //Create query to return all if data is matched
        $query = "SELECT * FROM products WHERE cd_artist = $search_artist AND cd_title = $search_title";
        $result = mysql_query($query);

        //Check to see how many rows exist
        $num = mysql_numrows($result);
        
        //Check to see if we found 1 row with that page name
        if( mysql_num_rows($result) == 1 ) {
        
        //Store each returned column in a variable
        $cd_artist = mysql_result ($result, 0, "cd_artist");
        $cd_title = mysql_result ($result, 0, "cd_title");
        $cd_price = mysql_result ($result, 0, "cd_price");
        $cd_image_name = mysql_result ($result, 0, "cd_image_name");
    
                echo ("
                <form>
                    <table>
                        <tr>
                            <td>Artist:</td>
                            <td><input type=\"text\" name=\"\" value=\" $cd_artist \" /></td>
                        </tr>
                        
                        <tr>
                            <td>Title:</td>
                            <td><input type=\"text\" name=\"\" value=\" $cd_title \" /></td>
                        </tr>
                        
                        <tr>
                            <td>Price</td>
                            <td><input type=\"text\" name=\"\" value=\" $cd_price \" /></td>
                        </tr>
                        
                        <tr>
                            <td>Img Name</td>
                            <td><input type=\"text\" name=\"\" value=\" $cd_image_name \" /></td>
                        </tr>
                        
                        <tr>
                            <td>&nbsp;</td>
                            <td><input type=\"submit\" name=\"\" value=\"Submit\" />
                        </tr>
                    </table>
                </form>");
            
            
                
                    } else {
                     
                    //Error array
                    $errors = array();
                
                    //Check that the following exist
                    if(!$cd_artist) {
                        $errors[] = "<strong>Artist not found!</strong>";
                    }
                    
                    if(!$cd_title) {
                        $errors[] = "<strong>Title not found!<br /></strong>";
                    }
                    
                    //Split errors up and show them
                    if (count($errors) > 0) {
                        foreach($errors AS $error) {
                            echo $error . "<br>\n";
                            }
                        }
                        
                    //GO to....
                    header('Refresh: 3; url=UpdateExistingProduct.php');
                    }    
?>

When I enter numbers into the forum (example an entry with Artist = 1 and Title =2) it will return the results in the the other form. When I use letters instead (example an entry with Artist = a and Title = b) it won't work. I'm presented with two errors and I'm not sure why...

Quote:

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in C:\WOS\www\TUCS\do_update.php on line 21

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\WOS\www\TUCS\do_update.php on line 24
Artist not found!

I know there's quite a lot of code there, sorry.

Alan @ CIT · 02-28-2008, 12:50 AM

The problem there is that you are not wrapping $search_artist and $search_title in single quotes within your SQL statement.

For example, at the moment, MySQL sees your query as:

Code:

SELECT * FROM products WHERE cd_artist = a AND cd_title = b

Because the letters a and b are not wrapped in single quotes, MySQL assumes that they must be an SQL keyword. This doesn't happen when you use numbers because MySQL knows that a number is not an SQL keyword.

To resolve the problem, you need to wrap your variables in single quotes:

PHP Code:


			
$query = "SELECT * FROM products WHERE cd_artist = '$search_artist' AND cd_title = '$search_title'";

MySQL would now see your query as:

Code:

SELECT * FROM products WHERE cd_artist = 'a' AND cd_title = 'b'

Which it understands

Alan

StevenF · 02-28-2008, 12:52 AM

Thank you so much, Alan. I've been trying to figure it out for a while now.

I love PHP - always learning something new. Right now I'm trying to develop my own very basic shopping cart system. I'm very new to PHP so it's a big challenge.

Thanks again!

Alan @ CIT · 02-28-2008, 08:07 AM

Challanges are the best thing about programming

Once you know everything I'd imagine it would get boring. Thankfully, I'm still learning new things everyday so it's sill fun

Good luck with the shopping cart!

Alan

StevenF · 02-28-2008, 10:49 PM

I've got another slight problem. Something slightly different. I have the following code:

PHP Code:


			
    if (isset($_POST['submit']))
    {
    
    //Creating a query that enters the data in the above variables into a DB
    $query = 'INSERT INTO cart SET        pass_cdID = "'.mysql_real_escape_string($pass_cdID).'",
                                        pass_artist = "'.mysql_real_escape_string($pass_artist).'",
                                        pass_title = "'.mysql_real_escape_string($pass_title).'",
                                        pass_price = "'.mysql_real_escape_string($pass_price).'",
                                        pass_image_name = "'.mysql_real_escape_string($pass_image_name).'"';
    
    //execcute a query on a MySQL database
    mysql_query($query);
    
    }

Now that query should only be executed if the submit button is pressed on the previous page. It does that, but if you then refresh the page, it runs the query again. Obviously I don't want it to do this, any ideas?

Alan @ CIT · 02-28-2008, 10:55 PM

That's a common problem unfortunately, Web browsers aren't smart enough to know that you have already submitted the form so it submits it again.

There are 2 ways you can get around the problem, the first is to redirect the visitor to another page directly after your INSERT query. If they then refresh the page it doesn't matter as it's a different page. You could probably also redirect the user (using header()) to the same page they are on - this would also resolve the problem.

The second option is to do a quick SELECT query before you do your INSERT query to see if the record has already been inserted - if it has, skip the insert query.

Alan

StevenF · 02-29-2008, 12:32 AM

Once again, thank you very much, Alan. After messing around for half an hour or so, I've managed to get it to work doing the following:

PHP Code:


			
//Select all from cart table where the cdID clicked matches cdID in DB

    $select_cdID = "SELECT * FROM cart WHERE pass_cdID = '$pass_cdID'";

    $cdID_result = mysql_query($select_cdID);

    

    //Check to see how many rows exist

    $returned_cdID = mysql_numrows($cdID_result);

    

    //If more than one row  has been effected...

    if(mysql_affected_rows() > 0)

    {

  

        //Store pass_cdID column in a variable

        $store_cdID = mysql_result ($cdID_result, "pass_cdID");

        

    }

    

    if ($pass_cdID != $store_cdID) {

    

    

    if (isset($_POST['submit']))

    {

    

    //Creating a query that enters the data in the above variables into a DB

    $query = 'INSERT INTO cart SET        pass_cdID = "'.mysql_real_escape_string($pass_cdID).'",

                                        pass_artist = "'.mysql_real_escape_string($pass_artist).'",

                                        pass_title = "'.mysql_real_escape_string($pass_title).'",

                                        pass_price = "'.mysql_real_escape_string($pass_price).'",

                                        pass_image_name = "'.mysql_real_escape_string($pass_image_name).'"';

    

    //execcute a query on a MySQL database

    mysql_query($query);

    

    }     

                                    }

I have a variable called pass_cdID which comes from the product added to cart. Then I pulled the pass_cdID data from the database and stored that in a variable. The said:

PHP Code:


			
if ($pass_cdID != $store_cdID) {

//execute query

}

That's what I love about this, it makes you think!

Thanks again,
Steven