PHP/mysql_query problems

[Rian19]

Hi

I'm just starting with php programming and need some help. This is just a test script I'm writing to see how it works.

I'm trying to search for phone of a certain make and price using a different search box for Make and price.

I want the results to return all the phones that is of a certain Make and below a certain price. And also if just make is searched for that all the ones with that make must be returned. The same with price.

I have been searching all over the internet and trying hundreds of different things but it just doesn't want to work.

Here is my code so far.

<?php
//connect to database
mysql_connect('localhost', 'rian', 'ri0401')
or die(mysql_error('Could not connect'));
mysql_select_db("test")
or die(mysql_error('Could not select database'));

//Identify verubles
$make = $_POST['Make'];
$price = $_POST['Price'];

//query
$sql=mysql_query("Select *
     From test
	 Where Price <= '.%$price%.'
	 AND 
	 Make Like '".$make."'")
	or die(mysql_error());

while ($row=mysql_fetch_array($sql)){
	echo $row['Make'];
	echo '<br/>';
	echo $row['Model'];
	echo '<br/>';
	echo $row['Price'];
	echo '<br/><br/>';
}
?>

Any help will be appreciated.

Thanks
Rian

[ajbest]

<?php
//connect to database
mysql_connect('localhost', 'rian', 'ri0401')
or die(mysql_error('Could not connect'));
mysql_select_db("test")
or die(mysql_error('Could not select database'));

//Identify verubles
$make = $_POST['Make'];
$price = $_POST['Price'];

//query
$sql=mysql_query("Select *
     From test
	 Where Price <= '".$price."'
	 AND 
	 Make Like '%".$make."%'")
	or die(mysql_error());

while ($row=mysql_fetch_array($sql)){
	echo $row['Make'];
	echo '<br/>';
	echo $row['Model'];
	echo '<br/>';
	echo $row['Price'];
	echo '<br/><br/>';
}
?>

looks like you had some % signs in the wrong place on your query. Try this and post back the sql error if it still doesn't work.

[rotten69]

Good practice is to have something like this in mind.. If you're dealing with DB systems, connection_name.open at the top and once all queries and things are done with the DB then close it... connection_name.close

please check the php manual site for more info on how to open/close connection.. php.net

[Rian19]

Thanks, ajbest

There was no error. The only problem is when you only add a Make search no results is returned. When you add both make and price the results is returned correctly. And also if you just enter a Price search.

[ajbest]

You will have to use a couple if statements to construct your query. Something like this

//initialize the same way with your db connection
$query="SELECT * FROM test WHERE";
if (isset($_POST['make'])){
   $query.=" Make LIKE '%".mysql_real_escape_string($_POST['make'])."%' AND"
}
if (isset($_POST['price']{
   $query.=" Price <= '".mysql_real_escape_string($_POST['price'])."'";
} 
else {$query=substr($query, 0, -3);}// this takes out the and statement in the event price was not posted

if (!isset($_POST['make'], $_POST['price'])){$query=substr($query, 0, -5);}//this takes out the where statement in the event neither the price nor make was posted

$sql=mysql_query($query) or die (mysql_error());
//continue with same display

Note that I have also implemented mysql_real_escape_string. This function will sterilize the post vars to prevent potential sql injection attacks. It is always best to not trust any user input. Assume everyone is a hacker and your code will end up a lot safer.

[Rian19]

I tried that but now it just returns everything in the database regardless of what you enter.

Here is the code. I might have done something wrong.

<?php
//connect to database
mysql_connect('localhost', 'rian', 'ri0401')
or die(mysql_error('Could not connect'));
mysql_select_db("test")
or die(mysql_error('Could not select Database'));

//Identify verubles
$make = $_POST['Make'];
$price = $_POST['Price'];

//query
$query=("Select * From test Where");
if (isset($_POST['make'])){
	$query.="  Make LIKE '%".mysql_real_escape_string($_POST['make'])."%' AND";
}
if (isset($_POST['price'])){
	$query.=" price <= '".mysql_real_escape_string($_POST['price'])."'";
}
else {$query=substr($query, 0, -3);}

if (!isset($_POST['make'], $_POST['price'])){$query=substr($query, 0, -5);}

$sql=mysql_query($query) or die (mysql_error());
while ($row=mysql_fetch_array($sql)){
	echo $row['Make'];
	echo '<br/>';
	echo $row['Model'];
	echo '<br/>';
	echo $row['Price'];
	echo '<br/><br/>';
}
?>

[Rian19]

I was wondering if you can't do something like this.

//query
if (isset($_POST['make'])){
	$query.="  Make LIKE '%".mysql_real_escape_string($_POST['make'])."%'";
}
if (isset($_POST['price'])){
	$query.=" price <= '".mysql_real_escape_string($_POST['price'])."'";
}

if (isset($_POST['make'], $_POST['price'])){
	$query.="Select *
     From test
	 Where Price <= '".mysql_real_escape_string($price)."'
	 AND 
	 Make Like '%".mysql_real_escape_string($make)."%'"
	or die(mysql_error());
}
$sql=mysql_query($query) or die (mysql_error());

Where you change the query depending on what was entered.
But it has an error saying "Undefined variable: query in C:\wamp\www\Test\Untitled-1.php on line 28"

[Stefano Mtangoo]

Since you are beginning, it is better you forget mysql_** and use mysqli or the more portable PDO. I would recommend PDO after reading several threads and try it out. Here is a workig script I did for a thread in this forum:

change username password and db to match yours

<html>
<head>

</head>
<body>
<?php
    ini_set("display_errors", 1);//error mgt
    try{
        $db = new PDO("mysql:host=localhost;dbname=xxx", "xxxx", "xxxx");
    }catch(PDOException $e){        
        echo $e->getMessage();
    }
    
    $stmt=$db->prepare("INSERT INTO likesys(liked) VALUES(:like)");
    $stmt2=$db->prepare("SELECT SUM(liked) AS likes FROM likesys WHERE liked=:like");
    $display=false;
    if(isset($_POST["like"])){
        $stmt->execute(array(":like"=>"1")); 
        $display=true;
    }
    if(isset($_POST["dislike"])){
        $stmt->execute(array(":like"=>"-1")); 
        $display=true;
    }
    $stmt->closeCursor();
    
    //if($display){ 
        $stmt2->execute(array(":like"=>"1"));
        $res = $stmt2->fetch(PDO::FETCH_ASSOC);  
        echo "<p>Likes: ". $res['likes']."</p>";
        
        $stmt2->execute(array(":like"=>"-1"));
        $res = $stmt2->fetch(PDO::FETCH_ASSOC);  
        echo "<p>Dislikes: ". abs($res['likes'])."</p>";
   // }
    
    $db=null;
?>
    <form action=<?php echo $_SERVER["PHP_SELF"];?> method="POST" >
        <input name="like" type="submit" value="Like" />
        <input name="dislike" type="submit" value="Dislike" />
    </form>
</body>
</html>

[cwarn23]

But it has an error saying "Undefined variable: query in C:\wamp\www\Test\Untitled-1.php on line 28"

Don't forget to put the following at the beginning of your script.

$query='';