Select statement problem

[nade93]

Hi All

 

I am using a dropdown selection table that originally worked until i used the JOIN statement to combine two other tables. When i do this statement in MYSQL, then it returns results (without the '  ') then need to add '  ' around the values as below for the array to be valid.

 

$dropdown = empty($_POST['dropdown'])? die ("ERROR: Select from Dropdown") : mysql_escape_string($_POST['dropdown']); 
$query = "SELECT * FROM jos_community_fields_values FULL JOIN jos_community_users, jos_users WHERE user_id = 'userid' AND field_id ='2' AND value = '$dropdown'";

 

It is returning no results at present whatever i select from the dropdown. Can someone please help?

[kickstart]

Hi

 

You are not specifying any fields to do the tables in (indeed you are not specifying a join on jos_users at all).

 

You want something like this (although you will need to change it to do the join on the appropriate fields).

 

$dropdown = empty($_POST['dropdown'])? die ("ERROR: Select from Dropdown") : mysql_escape_string($_POST['dropdown']); 
$query = "SELECT * FROM jos_community_fields_values a JOIN jos_community_users b ON a.userid = b.userid JOIN jos_users c ON b.userid = c.userid WHERE user_id = 'userid' AND field_id ='2' AND value = '$dropdown'";

 

All the best

 

Keith

[nade93]

Hi Keith

 

thanks for the prompt reply

 

the user id on each of the tables is

a - user_id

b - userid

c- id

 

I have tried adjusting the statement you used to

SELECT * FROM jos_community_fields_values a JOIN jos_community_users b ON a.user_id = 'b.userid' JOIN jos_users c ON b.userid = 'c.id' WHERE user_id = 'userid' AND field_id ='2' AND value = '$dropdown'

 

Neither your code, nor this one work either

 

My full code is as follows:

 

if ($_POST['Submit']) {


$dropdown = empty($_POST['dropdown'])? die ("ERROR: Select from Dropdown") : mysql_escape_string($_POST['dropdown']);
$query = "SELECT * FROM jos_community_fields_values a JOIN jos_community_users b ON a.user_id = 'b.userid' JOIN jos_users c ON b.userid = 'c.id' WHERE user_id = 'userid' AND a.field_id ='2' AND a.value = '$dropdown'";
$result = mysql_query($query);

if ($row = mysql_fetch_array($result)) {
print"<br /><b>Your search returned ".mysql_num_rows($result)." results.</b><hr>"; // Print how many results the query returned.

$i = 1;

              do { // This loop outputs each result.

                     print"<br /><br />This is search result number {$i}"; // Here you can format the search results and display whatever you want about the result.

                     $i++;

              } while ($row = mysql_fetch_array($result));

       } else {

              print"<br />Your search returned no results.";

}
}
?>

[kickstart]

Hi

 

Do you get an error message? Can you echo out the SQL and try it directly in the database (eg, via phpmyadmin).

 

All the best

 

Keith

[nade93]

no error message and works in the admin panel,

 

on the server however, it goes to the "else" statement "your search returned no results.... i know i have missed a comma somewhere or something really simple, just cannot seem to find it!

[kickstart]

Hi

 

Think I have seen the issue.

 

You have inverted commas around the field names rather than back ticks (ie you have ' rather than `).

 

All the best

 

Keith

[nade93]

I had to remove the back ticks as it produced the following area

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource......

 

when i use the ' ' instead the array  error does not come up

 

 

[kickstart]

Hi

 

At least it is sorted. I tend to rarely use back ticks as I try and avoid any column names with spaces or that may be reserved words.

 

All the best

 

Keith

[nade93]

oh no its not sorted, im still not displaying any results from the statements above, i was just saying that using back ticks produces an error on the .php page

 

 

[fenway]

Echo the actual statement being sent to the server.