PHP/mySQL help

[greens85]

Hi All,

 

I inherited some code as part of a bought website, I am trying to convert it to make it better and add functionality... however I am having a hard time trying to figure out what is going on in one particular section of code.

 

In short there is basically a block of PHP which is producing a SQL statement, but the SQL always returns 0 results. I can't figure out why this is because I can't figure out what the PHP is producing.

 

Would someone with my knowledge than me be kind enough to take a look and advise me where possible?

 

This is the part doing the generating:

 

// IF THE CATEGORY FIELD IS NOT EMPTY
                        if (!empty($row['category'])) {
                            
                            // EXPLODE THE ARRAY ON ,
                            $strArr = explode(",",$row['category']);
                                
                                // FOR EACH ONE
                                for($i=0; $i<sizeof($strArr); $i++) {
                                    
                                    $str = trim($strArr[$i]);
                                    $strs .= "'".trim($strArr[$i])."',";
                                    $sch[] =  "category LIKE '%$str%'";
                                    
                                }
                                
                                echo "Categories in the array are: ". $strs;
                                echo "<br /><br />";
                                
                                    $strs = substr($strs,0,-1);                        
                                    $sch[] = "category in ($strs)";
                        }
                        
                        // IF THE SUBCOUNTY FIELD IS NOT EMPTY
                        if(!empty($row['subcounty'])) {
                            
                            // EXPLODE THE ARRAY ON ,
                            $strArr = explode(",",$row['subcounty']);
                            
                                // FOR EACH ONE    
                                for($i=0; $i<sizeof($strArr); $i++) {
                                    
                                    $str = trim($strArr[$i]);
                                    $strs2 .= "'".trim($strArr[$i])."',";
                                    $sch[] = "subcounty LIKE '%$str%'";
                                }
                                
                                echo "SubCounties in the array are: ". $strs2;
                                echo "<br /><br />";
                                
                                $strs2 = substr($strs2,0,-1);
                                $sch[] = "subcounty in ($strs2)";
                        }
                        
                        // IF THE TITLE FIELD IS NOT EMPTY
                        if(!empty($row['title'])) {
                                
                                // JOIN SEARCH TERM TO THE ARRAY
                                $sch[] = "(position LIKE '%$row[title]%' || description LIKE '%$row[title]%')";
                                
                                echo "The title is: ".$row['title'];
                                echo "<br /><br />";
                        
                        } 

 

And this is the SQL part that always returns blank:

 

// COUNT THE AMOUNT OF RESULTS THEN SELECT THE JOBS FROM THE DATABASE
                        $result = mysql_query("SELECT count(*) FROM jobs ". (($sch)?"WHERE ".join(" AND ", $sch):"")."");
                        $total = mysql_result($result, 0);
                        $r9 = mysql_query("SELECT * FROM jobs ".(($sch)?"WHERE ".join(" and ", $sch):"")." ORDER BY jobid DESC");
                        
                        echo "The count is: " . $total;
                        echo "<br /><br />";  

 

Any help would be appreciated,

 

Many thanks,

 

Greens85

[PFMaBiSmAd]

Form the final query in a php variable, so that you can echo what it actually is, then just put that variable into the mysql_query() statement -

 

$query = "SELECT count(*) FROM jobs ". (($sch)?"WHERE ".join(" AND ", $sch):"")."";
$result = mysql_query($query);

 

Just echo $query; on the next line after where it is formed to see what the query is.

[greens85]

Form the final query in a php variable, so that you can echo what it actually is, then just put that variable into the mysql_query() statement -

 

$query = "SELECT count(*) FROM jobs ". (($sch)?"WHERE ".join(" AND ", $sch):"")."";
$result = mysql_query($query);

 

Just echo $query; on the next line after where it is formed to see what the query is.

 

Hi,

 

Many thanks for your reply...

 

I get this output:

 

Resource id #7

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /path/to/file/alertTest.php on line 114

 

So I'm not actually seeing the sql result unfortunatley...

 

I have been experimenting since my post and came up with this:

 

$result = mysql_query("SELECT count(*) FROM jobs WHERE category IN ($strs)");

 

which actually works but when i try and add a second element i.e.

 

$result = mysql_query("SELECT count(*) FROM jobs WHERE category IN ($strs) AND subcounty IN ($strs)");

 

It fails!

[PFMaBiSmAd]

I get this output:

 

Resource id #7

 

^^^ Not if you did what I posted.

 

You echoed the $result variable. That has nothing to do with what I stated.

[greens85]

Ah, silly me...

 

The query actually looks fine,

 

SELECT count(*) FROM jobs WHERE category LIKE '%Business Manager/Bursar Jobs%' AND category LIKE '%Cover Supervisor Jobs%' AND category in ('Business Manager/Bursar Jobs','Cover Supervisor Jobs') AND subcounty LIKE '%London%' AND subcounty in ('London')

 

so maybe I am just trying it against the wrong records. Now I can see this, I should be able to figure it out...

 

Many thanks for your help.