[SOLVED] problem ahoy! SELECT statement again.

[bioTINMAN]

the select statement, given under, is throwing up problems. The $value is not being executed at the last statement (red colored one), even when there is no value to return. However, mysql_fetch_assoc gives out an error as expected.  I cant figure out where i went wrong.

 

 

<?php

 

include_once('connect.php');

 

$var=$HTTP_POST_VARS['search_choose'];

 

$enter=$HTTP_POST_VARS[$var];

 

$enter1=$var."='".$enter."'"; // this is so that i get a value like val='1234'

 

echo $var,$enter,$enter1;

 

 

if($var=='search_city' || $var=='search_city1')

{

 

$value=mysql_query("SELECT sub_number, name, address, country, email, Phone FROM personal WHERE city='$enter'");

 

}// if stops here

 

 

 

else

if($var=='sub_number' || $var=='name' || $var=='address' || $var=='country' ||$var=='state' || $var=='email' ||

 

$var=='Phone')

{

$value=mysql_query("SELECT sub_number, name, address, country, email, Phone FROM personal WHERE $enter1");

}// else if stops here

 

//checking whether the query has returned true or false

 

 

if(!$value) 

echo "query not found"; // there is no execution here

 

if(!$check_in=mysql_fetch_assoc($value))//this statement executes. why?

echo "nope, no results";

 

 

?>

[bioTINMAN]

and just for info sake : if i echo the $value, then it returns a Resource id,.

 

for that sake any such query to any database is returning true ALWAYS. There must be something terribly wrong with that code, or something to modify in MYSQL itself?

[teng84]

$check_in=mysql_fetch_assoc($value);

if(count($check_in) <=0)

echo "nope, no results";

 

// note you cant echo $value because that is the resouce data comming from your query so you will get that resource id messg

 

[bioTINMAN]

it works if i test the fetch assoc statement itself:

 

if(!$check_in=mysql_fetch_assoc($value))

echo "nope, no results";

 

but it doesnt work this way:

 

if(!$value)

echo "nope, no results";

 

and btw after i had to do a little modification to your statement:

 

$check_in=mysql_fetch_assoc($value);

if(count($check_in) <=1)

echo "nope, no results";

 

what is the 1 thing that is being returned?

[teng84]

sorry it should be <0

1 is the first row returned by fetching your db records.. note that fetch array returns single row so if you need more rows or you expect more rows you need to loop your array fetc eg..

 

while($row=mysql_fetch_array(resourcevalue here)){}

[bioTINMAN]

i didnt get you. Even if there are no results for that particular query, the if(!$value) gets executed.

 

and your statement, doesnt show anything when there is no result.

 

i mean to say, if i have a query like this one below:

 

$value=mysql_query("SELECT sub_number, name, address, country, email, Phone FROM personal WHERE city='london'");

 

but even though the database has no entry for london, its executing if($value) or even your (teng84) statements.

 

your statement is executed only when:

 

$check_in=mysql_fetch_assoc($value);

if(count($check_in) >0)

echo "nope, no results";

 

so its returning something, but the fact remains there is nothing to return! i am only able to make get teh correct reply with if(!$check_in=mysql_fetch_assoc($value))

 

another person on the net also has the same problem : http://www.webmasterworld.com/php/3444616.htm i have no idea whats wrong. do you think i have to do tweaks the mysql side? or is it plainly that im not getting what your trying to convey

[PHP_PhREEEk]

You want to check a couple of things...

 

Did your query succeed?

 

If so, did it contain any records?

 

These are two different things... an unsuccessful query dictates a MySQL connection problem, while an empty record set simply means nothing was found per your criteria (but the connection was successful).

 

So, let's create a string for the query, then test for a successful connection. Then we will see if any records are returned:

 

<?php
if ( $var == 'search_city' || $var == 'search_city1' ) {

    $sql = "SELECT `sub_number`, `name`, `address`, `country`, `email`, `Phone` FROM `personal` WHERE city='$enter'";
    if ( !$result=mysql_query($sql) ) { // did the connection with MySQL work?
        die('MySQL Error: ' . mysql_error());
    }
    if ( !mysql_num_rows($result) > 0 ) { // Is there one or more records?
        $nothing = 'No Records Found...';
    } else {
        // just throwing out records here... you would format your output in this section how you see fit.
        while ( $row = mysql_fetch_assoc($result) ) {
            echo "<pre>";
            print_r($row);
            echo "</pre><br>";
        }
    } 
}// if stops here

 

PhREEEk

[bioTINMAN]

thanks. i messed up my code big time. i tried it again with your inputs and this time it worked. thanks teng84 ,  PHP_PhREEEk for your time

[PHP_PhREEEk]

You're quite welcome!

 

We appreciate people who try their hardest to put up some code, and understand it. I feel that you have definitely taken something away from this besides working code, and that's exactly why we're here!

 

Happy trails... = ^)

 

PhREEEk