Search script problem

Question

MWEB 0 Junior Poster in Training

12 Years Ago

So i have this index.php code:

<form method="get" action="search.php">
<label>Search For: </label><input type="text" name="$res" />
<input type="submit" name="submit" value="Start Search" />
<input type="reset" value="Reset"
</form>

And this code in search.php

<?php
    $con = mysqli_connect('localhost','user', 'pass') or die(mysqli_error($con));
    mysqli_select_db($con, 'piese') or die(mysqli_error($con));
    #query database
    $query = mysqli_query($con, "SELECT `id`,`price` FROM `edit` ORDER BY `id`");
    #check for a result set
    if($query && mysqli_num_rows($query) > 0)
    {
        while($res = mysqli_fetch_assoc($query)) 
        {
            echo "{$res['id']}:The price is {$res['price']},<br />";
        }
    }
    else #if no result set, then output a message
    {
        echo 'No results.';
    }
    ?>

I search for an ID ( that have a value on database ) ex: 2345 and this show me all the rows i've in database .. Where i'm wrong?

php

5 Contributors
29 Replies
173 Views
1 Day Discussion Span
Latest Post 12 Years Ago Latest Post by AndreRet

AndreRet 526 Senior Poster

12 Years Ago

You are returning ALL records because you have not set a where value...

SELECT `id`,`price` FROM `edit` ORDER BY `id` WHERE `id`='2345'

adam.adamski.96155 43 Junior Poster

12 Years Ago

$query = mysqli_query($con, "SELECT id,price FROM edit WHERE id=$res");

...this was correct, but lacked the 'ORDER BY' clause, so :

$query = mysqli_query($con, "SELECT id, price FROM edit WHERE id=$res ORDER BY id");

...is all that needed changing, but when urtrivedi changed it to:

WHERE id='{$_GET['res]}'

...it went horribly wrong because it should be '{$_GET['res']}' (utrivedi missed a single quote after the s). I prefer to keep the MySQL statement clean and easy to read as Andreret demonstrated.
This is the bread and butter of PHP/MySQL web development, you should read a thousand tutorials until you can do this in your sleep.

AndreRet commented: Thanx +12

adam.adamski.96155 43 Junior Poster

12 Years Ago

@MWEB, you are drowning in confusion, lets go over it step by step.

Make a form, set the action to the page you want to process the details and the method(get).

Collect form variable/s ready for database search:
$res = $_GET['res'];

Make connection using error checking:

if(!$con = mysqli_connect($host, $user, $pass)){
    echo mysqli_error(); die();
    }

Select database using error checking:

if(!mysqli_select_db("db")) {
    echo mysqli_error(); die();
    }

Build database query:
$sql = "SELECT id, price FROM edit WHERE id='$res' ORDER BY id ")

Send query using error checking:

if(!$res = mysqli_query($sql)){ //error, kill script and tell me what is wrong.
    echo mysqli_error(); die();
} else { //no error continue...
    if(mysqli_num_rows($res)){//no error, but are there any matches?
        while($row = mysqli_fetch_assoc($res)){//there are matches, do the thing...  
            $id = $row['id'];//this step not essential, 
            $price = $row['price'];// but keeps code clean and easy to read.
            echo "$id: The price is $price,<br />"// <--see? clean, easy to read :D
        }
    } else {//no matches, say so
        echo "There were no matches to the search.";
    }
}

Pritaeas has written an excellent guide: http://www.daniweb.com/web-development/php/code/434480/using-phpmysqli-with-error-checking
If there are no DB errors and still no results, try running your query in PHPMyAdmin. You can echo your query to the screen:

$sql = "SELECT id, price FROM edit WHERE id='$res' ORDER BY id ");
echo $sql; die();

...and paste it directly into PHPMyAdmin to see if there is any difference.
Good luck! :D

AndreRet 526 Senior Poster

12 Years Ago

Only a pleasure. I'm sure you can add the price... :)

$price = $search_query['price'];

echo $searchid, ': The price is ', $search_query, '<br />';

Please mark as solved.

urtrivedi 276 Nearly a Posting Virtuoso

12 Years Ago

Line 16 above add price in select list

"SELECT `id`,price FROM `edit`  WHERE `id`='$searchid'"

line 34 use $search_row

echo $searchid, ': The price is ', $search_row['price'], '<br />';

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

MWEB 0 Junior Poster in Training · Answer 1 · 2012-10-23T16:53:42+00:00

but this value must be dinamyc .. i mean .. if I search 4321 the script will show me the 4321 Price .. I think I need a variable but i'm not sure ..

LastMitch · Answer 2 · 2012-10-23T16:56:43+00:00

@MWEB

When you SELECT.

You don't put ''

$query = mysqli_query($con, "SELECT 'id','price' FROM 'edit' ORDER BY 'id'");

it should be like this:

$query = mysqli_query($con, "SELECT id, price FROM edit ORDER BY id WHERE id=$2345");

-

SELECT column_name(s),column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC  
WHERE

MWEB 0 Junior Poster in Training · Answer 3 · 2012-10-23T17:18:27+00:00

So.. after the suggestions

The index.php:

<form method="get" action="search.php">
<label>Search For: </label><input type="text" name="res" />
<input type="submit" name="submit" value="Start Search" />
<input type="reset" value="Reset"
</form>

The search.php:

 <?php
        $con = mysqli_connect('localhost','user', 'pass') or die(mysqli_error($con));
        mysqli_select_db($con, 'database') or die(mysqli_error($con));
        #query database
       $query = mysqli_query($con, "SELECT id, price FROM edit ORDER BY id DESC WHERE id=res");
        #check for a result set
        if($query && mysqli_num_rows($query) > 0)
        {
            while($res = mysqli_fetch_assoc($query)) 
            {
                echo "{$res['id']}:The price is {$res['price']},<br />";
            }
        }
        else #if no result set, then output a message
        {
            echo 'No results.';
        }
        ?>

and the result is.. whatever i try to search the result is no-result

LastMitch · Answer 4 · 2012-10-23T17:30:44+00:00

@MWEB

Your query is wrong

Instead of this:

if($query && mysqli_num_rows($query) > 0)

change it to this

if($query->mysqli_num_rows > 0){

while($res = mysqli_fetch_assoc($query)){

echo "{$res['id']}:The price is {$res['price']},<br />";

}}

else #if no result set, then output a message{

echo 'No results.';
}

Try it now.

MWEB 0 Junior Poster in Training · Answer 5 · 2012-10-23T18:43:30+00:00

On your code is an error

[23-Oct-2012 18:36:46 UTC] PHP Parse error:  syntax error, unexpected T_ELSE in /home/pieseaut/public_html/FiltreAutoMobile/search.php on line 11

Is at else#if .. so i remove the else ad now the page is blank .. without any results and without the No results text ..
The problem can be at the identification of what the user search?

LastMitch · Answer 6 · 2012-10-23T19:41:33+00:00

@MWEB

Did you write this part if so how did you come up with this?

   if($query && mysqli_num_rows($query) > 0)
    {
        while($res = mysqli_fetch_assoc($query)) 
        {
            echo "{$res['id']}:The price is {$res['price']},<br />";
        }
    }
    else #if no result set, then output a message
    {
        echo 'No results.';
    }

-

The code I provide has an error on line 11 means my { was not there

Instead of this:

else #if no result set, then output a message{

it should be this:

else #if no result set, then output a message 
{

For this no single qoute

echo 'No results.';

It Should be this double qoute

echo "No results.";

Try this again:

  if($query->mysqli_num_rows > 0){

  while($res = mysqli_fetch_assoc($query)){

  echo "{$res['id']}:The price is {$res['price']},<br />";

  }}

  else #if no result set, then output a message 
  {

  echo "No results.";

 }

MWEB 0 Junior Poster in Training · Answer 7 · 2012-10-24T06:42:31+00:00

So .. after this it looks like

Index.php

<form method="get" action="search.php">
<label>Search For: </label><input type="text" name="$res" />
<input type="submit" name="submit" value="Start Search" />
<input type="reset" value="Reset"
</form>

Search.php

<?php
    $con = mysqli_connect('localhost','user', 'pass') or die(mysqli_error($con));
    mysqli_select_db($con, 'database') or die(mysqli_error($con));

    #query database
   $query = mysqli_query($con, "SELECT id,price FROM edit ORDER BY id DESC WHERE id=res");

    #check for a result set
   if($query->mysqli_num_rows > 0){

while($res = mysqli_fetch_assoc($query)){

  echo "{$res['id']}:The price is {$res['price']},<br />";
  }}


  else #if no result set, then output a message 
  {

echo "No results.";

  }

The result is "No results" for every search.. .. I think is something wrong at query .. that Where..Or maybe at the search form:-?? I think the script didn't get corectly what user type in the search box

urtrivedi 276 Nearly a Posting Virtuoso · Answer 8 · 2012-10-24T06:57:21+00:00

First of all you must fix search text box name to something (dont use dollor sign)
line 2 in index.php must look like (without dollor in name)

<label>Search For: </label><input type="text" name="res" />

in search.php, you query is failing becaseu i guess you using res word in query, it shold loook like following
line 6 in search.php

"SELECT id,price FROM edit ORDER BY id DESC WHERE id='{$_GET['res]}'"

MWEB 0 Junior Poster in Training · Answer 9 · 2012-10-24T07:36:02+00:00

@urtrivedi
First of all : Thank's :) I think that is the problem
But it gives errors

[24-Oct-2012 07:33:29 UTC] PHP Parse error:  syntax error, unexpected '"', expecting ']' in /home/search.php on line 5

The page again:

<?php
    $con = mysqli_connect('localhost','user', 'pass') or die(mysqli_error($con));
    mysqli_select_db($con, 'database') or die(mysqli_error($con));
    #query database
   $query = mysqli_query($con, "SELECT id,price FROM edit ORDER BY id DESC WHERE id='{$_GET['res]}'");
    #check for a result set
   if($query->mysqli_num_rows > 0){
while($res = mysqli_fetch_assoc($query)){
  echo "{$res['id']}:The price is {$res['price']},<br />";
  }}
  else #if no result set, then output a message 
  {
echo "No results.";
  }

AndreRet 526 Senior Poster · Answer 10 · 2012-10-24T09:00:23+00:00

Try the following -

<?php
    $res = $_POST['res']; //If no orm is posted, use $_GET['res'];
    $price = $_POST['price']; //If no orm is posted, use $_GET['price'];

    $con = mysqli_connect('localhost','user', 'pass') or die(mysqli_error($con));
    mysqli_select_db($con, 'database') or die(mysqli_error($con));
    #query database
   $query = mysqli_query($con, "SELECT id,price FROM edit WHERE id=$res");

    #check for a result set
    if($query->mysqli_num_rows > 0){
        while($res = mysqli_fetch_assoc($query)){
        echo $res, ':The price is $ ', $price, '<br />';
    } else //if no result set, then output a message 
        {
            echo 'No results.';
        }

urtrivedi 276 Nearly a Posting Virtuoso · Answer 11 · 2012-10-24T09:29:05+00:00

"SELECT id,price FROM edit  WHERE id='{$_GET['res]}' ORDER BY id DESC"

order by must come after where clause

MWEB 0 Junior Poster in Training · Answer 12 · 2012-10-24T10:04:03+00:00

@urtrivedi

$query = mysqli_query($con, "SELECT id,price FROM edit  WHERE id='{$_GET['res]}' ORDER BY id ");

This error: PHP Parse error: syntax error, unexpected T_STRING, expecting ']'

@AndreRet

You forget a } on line 14 . After i've put this the script give me No results for every search.

urtrivedi 276 Nearly a Posting Virtuoso · Answer 13 · 2012-10-24T11:09:54+00:00

I ADDED single quote after res in get

$query = mysqli_query($con, "SELECT id,price FROM edit  WHERE id='{$_GET['res']}' ORDER BY id ");

MWEB 0 Junior Poster in Training · Answer 14 · 2012-10-24T11:35:35+00:00

So.. after all these the search.php :

<?php


$con = mysqli_connect('localhost','user', 'pass') or die(mysqli_error($con));
    mysqli_select_db($con, 'database') or die(mysqli_error($con));


#query database
   $query = mysqli_query($con, "SELECT id,price FROM edit  WHERE id='{$_GET['res']}' ORDER BY id ");

 #check for a result set
   if($query->mysqli_num_rows > 0){
        while($res = mysqli_fetch_assoc($query)){
            echo "{$res['id']}:The price is {$res['price']},<br />";
}}
            else #if no result set, then output a message 
  {
                echo "No results.";
  }

and index.php

<form method="get" action="search.php">
<label>Search For: </label><input type="text" name="res" />
<input type="submit" name="submit" value="Start Search" />
<input type="reset" value="Reset"
</form>

And it gives me "No results" for every id I search..

I've put this at line 18 this

echo "No results for $res.";

and I saw this $res isn't what i've searched ..

urtrivedi 276 Nearly a Posting Virtuoso · Answer 15 · 2012-10-24T11:45:08+00:00

what you type in search box, do you type id say 1 or 2 or 3

or

you type description like computer, mouse, keybard

MWEB 0 Junior Poster in Training · Answer 16 · 2012-10-24T12:17:03+00:00

The id is a number like .. 450540312 but it can be ZR 5482123 too .. is VARCHAR..

urtrivedi 276 Nearly a Posting Virtuoso · Answer 17 · 2012-10-24T12:27:52+00:00

urtrivedi 276 Nearly a Posting Virtuoso

12 Years Ago

so you typing ID In search box right?

MWEB 0 Junior Poster in Training · Answer 18 · 2012-10-24T13:18:30+00:00

@urtivedi
Yes ..

@adam.adamski.96155

PHP Parse error:  syntax error, unexpected T_DNUMBER on line : if(!$con = mysqli_connect($host, $user, $pass)){

AndreRet 526 Senior Poster · Answer 19 · 2012-10-24T14:15:17+00:00

Firstly you do not need "ORDER BY id" because you will have only one id returned... You can order it by say price etc...

This code below was tested just now and works 100%...

<?php

//Open connection to database...
mysql_connect("localhost", "user", "pass") or
    die("Could not connect to my test site - : " . mysql_error());

mysql_select_db("yourdatabasenamehere");

//Get posted results from form...
if (isset($_POST['res'])) {
    $searchid = $_POST['res'];

    //query database...
    $search_query = mysql_query("SELECT `id` FROM `tablenamehere`  WHERE `id`='$searchid'");
    //Enclosed in single quote marks '' because the returned value might be more than an integer (number) - i.e. ZR 1234...

    $search_result = $search_query;

    //if (!$search_result){
       //echo 'Error in loading your search criteria. Please refresh page and try again';
    //}

    //Check if a value was given...
    if (empty($searchid)) {
        echo 'Please enter valid search criteria';

        exit(); 
        } else {
            while ($search_row = mysql_fetch_assoc($search_query)) {
                    echo $searchid, ': The price is ', '<br />';
            }
        }
    }
?>

<body>
<form method="POST" action="search.php">
    <label>Search For: </label>
    <input type="text" name="res" id="res" />
    <input type="submit" name="submit" value="Start Search" />
    <input type="reset" value="Reset" name="reset" />
</form>
</body>

Also note that I have not made use of mysqli, but mysql..

MWEB 0 Junior Poster in Training · Answer 20 · 2012-10-24T15:04:35+00:00

@ AndreRet
Thank's a lot

Works partially .. the last echo must display price of the searched ID

MWEB 0 Junior Poster in Training · Answer 21 · 2012-10-24T16:22:11+00:00

Sorry but it didn't work ..

The all code look like :

<?php


//Open connection to database...
mysql_connect("localhost", "user", "pass") or
    die("Could not connect to my test site - : " . mysql_error());
mysql_select_db("database");


//Get posted results from form...
if (isset($_POST['res'])) {
    $searchid = $_POST['res'];


//query database...
    $search_query = mysql_query("SELECT `id` FROM `edit`  WHERE `id`='$searchid'");
    $price = $search_query['price'];

//Enclosed in single quote marks '' because the returned value might be more than an integer (number) - i.e. ZR 1234...
    $search_result = $search_query;


//if (!$search_result){
       //echo 'Error in loading your search criteria. Please refresh page and try again';
    //}


 //Check if a value was given...
    if (empty($searchid)) {
        echo 'Please enter valid search criteria';
        exit(); 
        } else {
            while ($search_row = mysql_fetch_assoc($search_query)) {
                   echo $searchid, ': The price is ', $search_query, '<br />';
            }
        }
    }
?>

and:

<form method="POST" action="search.php">
    <label>Search For: </label>
    <input type="text" name="res" id="res" />
    <input type="submit" name="submit" value="Start Search" />
    <input type="reset" value="Reset" name="reset" />
</form>

The results for every search is Resource id #2
EX:

41 004 87 193: The price is Resource id #2
45 037 77 015: The price is Resource id #2
42 034 67 133: The price is Resource id #2
41 053 11 313: The price is Resource id #2

MWEB 0 Junior Poster in Training · Answer 22 · 2012-10-24T16:40:15+00:00

MWEB 0 Junior Poster in Training

12 Years Ago

SOLVED .. THANK YOU ALL FOR THE HELP :)

AndreRet 526 Senior Poster · Answer 23 · 2012-10-24T16:40:22+00:00

As urtrivedi pointed out, you never made a call to price. Also, my bad, calling price NOT $search_query...

<?php
//Open connection to database...
mysql_connect("localhost", "user", "pass") or
    die("Could not connect to my test site - : " . mysql_error());
mysql_select_db("database");
//Get posted results from form...
if (isset($_POST['res'])) {
    $searchid = $_POST['res'];
//query database...
    $search_query = mysql_query("SELECT `id`, `price` FROM `edit` WHERE `id`='$searchid'");
    $price = $search_query['price'];
//Enclosed in single quote marks '' because the returned value might be more than an integer (number) - i.e. ZR 1234...
    $search_result = $search_query;
//if (!$search_result){
       //echo 'Error in loading your search criteria. Please refresh page and try again';
    //}
 //Check if a value was given...
    if (empty($searchid)) {
        echo 'Please enter valid search criteria';
        exit(); 
        } else {
            while ($search_row = mysql_fetch_assoc($search_query)) {
                   echo $searchid, ': The price is ', $price, '<br />';
            }
        }
    }
?>

AndreRet 526 Senior Poster · Answer 24 · 2012-10-24T16:42:45+00:00

AndreRet 526 Senior Poster

12 Years Ago

Only a pleasure, happy coding. :)