Php Paradise, but no one can help???

[attaman]

I simplified this as much as possible. I got the key php code from one of the guru responses under a past forum help posting. The crux of the matter is this:
I can't resolve an Error when using a variable or SESSION var to store a sql string: the error is... "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource". If I place the actual sql text string in the mysql_query() function, it gives the $result object and outputs from the database fine. But, when I save the string into a var, and add that to the sql string, wholaaa! the error. One of the gurus has to know the answer to this. Code follows:

[code]
<?php

session_start();

include('includes/admin_functions.php');

//This is the code from one of the gurus:
$_SESSION['schooldist'] = $_GET['schooldist'];
$_SESSION['bedrooms'] = $_GET['bedrooms'];
$_SESSION['baths'] = $_GET['baths'];

//Create array to hold search parameters
//Keep it simple just include one for now
$LookFor = Array('schooldist', 'bedrooms', 'baths');

// See if the session variable name is in the array $LookFor
// Make sure the variable is not empty
// Add a match to a new array called $matches
foreach ($_SESSION as $key => $SessionVar) {
    if ((in_array($key, $LookFor)) && ($SessionVar != '')) {
          $matches[] = $key . ' = \'$_SESSION['.$key.']\'';
    }
}

// Join the matches with the word "AND" so this can be used as part of a query
// add later with more search params ...      
$where = implode(' AND ', $matches);

// Test to see if we are getting what is expected
print_r($where);  // Outputs  schooldist = '$_SESSION[schooldist]' AND bedrooms = '$_SESSION[bedrooms]' AND baths = '$_SESSION[baths]'

// so far, so good... thank you guru!  but it soon breaks when you run it in the query...

db_connect();

$result = mysql_query("select * from PROPERTY '".$where."';");

//ERROR!!!  Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in search_results.php on line 72

while( $row = mysql_fetch_array($result) ) {
    
    $vschooldist = $row['schooldist'];
    
    print("<table><tr><td width='100' align='center'>$vschooldist</td></tr></table>
  ");
}


?>

[/code]

[hitman6003]

Look at the query your creating....

[code]mysql_query("select * from PROPERTY '".$where."';");[/code]

and

[code]schooldist = '$_SESSION[schooldist]' AND bedrooms = '$_SESSION[bedrooms]' AND baths = '$_SESSION[baths]'[/code]

If you echo that out, you get something like:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']select[/span] * [color=green]from[/color] [color=orange]PROPERTY[/color] schooldist [color=orange]=[/color] [color=red]'somevalue'[/color] [color=blue]AND[/color] bedrooms [color=orange]=[/color] [color=red]'somevalue'[/color] [color=blue]AND[/color] baths [color=orange]=[/color] [color=red]'somevalue'[/color] [!--sql2--][/div][!--sql3--]

What's missing from the above? (the WHERE)

The easiest thing to do when you get that error, is add "or die(mysql_error())" to the end of your mysql_query line:

[code]$result = mysql_query("select * from PROPERTY '".$where) or die(mysql_error());[/code]

[attaman]

[!--quoteo(post=359462:date=Mar 28 2006, 03:51 PM:name=hitman6003)--][div class=\'quotetop\']QUOTE(hitman6003 @ Mar 28 2006, 03:51 PM) [snapback]359462[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Look at the query your creating....

[code]mysql_query("select * from PROPERTY '".$where."';");[/code]

and

[code]schooldist = '$_SESSION[schooldist]' AND bedrooms = '$_SESSION[bedrooms]' AND baths = '$_SESSION[baths]'[/code]

If you echo that out, you get something like:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']select[/span] * [color=green]from[/color] [color=orange]PROPERTY[/color] schooldist [color=orange]=[/color] [color=red]'somevalue'[/color] [color=blue]AND[/color] bedrooms [color=orange]=[/color] [color=red]'somevalue'[/color] [color=blue]AND[/color] baths [color=orange]=[/color] [color=red]'somevalue'[/color] [!--sql2--][/div][!--sql3--]

What's missing from the above? (the WHERE)

The easiest thing to do when you get that error, is add "or die(mysql_error())" to the end of your mysql_query line:

[code]$result = mysql_query("select * from PROPERTY '".$where) or die(mysql_error());[/code]
[/quote]


Many thanks... ouch! the only thing that is more painful than my bruised ego is the number of hours I have spent trying to work this out... I made your suggested changes
as thus...

[code]
$result = mysql_query("select * from PROPERTY where '".$where."';") or die("Couldn't query " . mysql_error());
[/code]

Now, I'm getting the following error:

//Couldn't query You have an error in your SQL syntax near '$_SESSION[schooldist]' AND bedrooms = '$_SESSION[bedrooms]' AND baths = '$_SESSI' at line 1

I've tried several things, but to no avail... any other ideas???

[toplay]

Change this:

$matches[] = $key . ' = \'$_SESSION['.$key.']\'';


to this:

$matches[] = $key . " = '" . $_SESSION[$key] . "'";

[attaman]

Thanks for the added assist...
I changed to
[code] $matches[] = $key . " = '" . $_SESSION[$key] . "'";

print_r($where);  [/code]
// Now reads schooldist = '49' AND bedrooms = '2' AND baths = '2'
// Good sign in that it is retrieving the form values of 49 and 2, but....

I still get this error...

Couldn't query You have an error in your SQL syntax near '49' AND bedrooms = '2' AND baths = '2'' ' at line 1

I noticed the last key-value pair value of 2 has an extra quote "
Any idea where it came from and how to fix???

[hitman6003]

Echo out your full query so that you can see it. If you don't see the problem, post it for us.

[toplay]

Get rid of the quotes in the query. Change this:

[code]
$result = mysql_query("select * from PROPERTY where '".$where."';") or die("Couldn't query " . mysql_error());
[/code]

to this:

[code]
$result = mysql_query('select * from `PROPERTY` where ' . $where) or die("Couldn't query " . mysql_error());
[/code]

[attaman]

Many thanks... I seem to have fixed it, but not certain why it wouldn't work the original way. Like to find out. Seems my way of accessing the sql string in the $where variable was producing the problem ($where worked, but '".$where."' didn't. To fix, I used...

$result = mysql_query("select * from PROPERTY where $where ;") or die("Couldn't query " . mysql_error());

Instead of...

$result = mysql_query("select * from PROPERTY where '".$where."' ;") or die("Couldn't query " . mysql_error());

I appreciate your time to help. Mucho Thanks. I discovered your site a few days ago and it is really quite good... the best I've seen so far. Now, if I can just learn PHP, maybe I can return the favor!




FYI, heres what my debug output was:

print_r($where); //produced the following

schooldist = '49' AND bedrooms = '3'

//echo of...
[code]
echo "select * from PROPERTY where '".$where."';";
[/code]

//Produced the following...
select * from PROPERTY where 'schooldist = '49' AND bedrooms = '3'' ;
NOTE the inconsistent quotes around value 3

//Error produced
Couldn't query You have an error in your SQL syntax near '49' AND bedrooms = '3'' ' at line 1

[khendar]

So its working now ?

The inconsistency in your debug output is because you are adding extra quotes around the $where clause when you echoed it.

[attaman]

Works great. This is a small fix that I'm certain will make the bigger piece finally work. Thanks so much. Amazing what a little help from the pros can do in a short amount of time.