displaying a complex mysql query with php

[stewdawg35]

I have completed my mysql query and it is as follows:

 

SELECT distinct xcart_extra_field_values.value FROM xcart_products, xcart_products_categories, xcart_extra_field_values WHERE xcart_products.productid=xcart_products_categories.productid AND xcart_products_categories.categoryid=314 AND xcart_products.productid=xcart_extra_field_values.productid AND xcart_extra_field_values.fieldid=1 ORDER BY xcart_extra_field_values.value;

 

However I am having difficulty displaying it with php...in fact I am not even sure if I am approaching it correctly (well obviously I am not because it does not work lol)

 

Here is what I have thus far (note: it doesn't work)

 

    <?php 
// Establish DB connection
 $host = "localhost";
 $user = "db_user";
 $pass = "db_pass";
 $dbname = "db_name";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname); 

 
// Formulate query
 $query = sprintf("SELECT distinct xcart_extra_field_values.value FROM xcart_products, xcart_products_categories, xcart_extra_field_values WHERE xcart_products.productid='xcart_products_categories.productid' AND xcart_products_categories.categoryid='314' AND xcart_products.productid='xcart_extra_field_values.productid' AND xcart_extra_field_values.fieldid='1' ORDER BY xcart_extra_field_values.value LIMIT 0, 30", mysql_real_escape_string($result));

// Perform Query
$result = mysql_query($query);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
  $message  = 'Invalid query: ' . mysql_error() . "\n";
  $message .= 'Whole query: ' . $query;
  die($message);
  }
  
// Use result
while ($row = mysql_fetch_assoc($result)) {
echo $row['result'];
}

// Free the resources associate with the result set
mysql_free_result($result);
?>

 

Now I need to display the results in 2 columns but with the above code I am just trying to display anything period. I have tested the db connection and sql query and they are correct it is just my php that needs some tweaking.

 

I really appreciate any help.

 

Thanks

[ryanlwh]

the $row part for mysql_fetch_assoc are the column names returned. so it should be $row['value']. let's first get the echo to work before making two columns.

[obsidian]

you are querying for 'value', and you're echoing 'result'... you need to either adjust your SQL to:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] value AS result...

[!--sql2--][/div][!--sql3--]

 

or, change your PHP to this:

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

[span style=\"color:#0000BB\"]<?php

[/span][span style=\"color:#007700\"]echo [/span][span style=\"color:#0000BB\"]$row[/span][span style=\"color:#007700\"][[/span][span style=\"color:#DD0000\"]\'value\'[/span][span style=\"color:#007700\"]];

[/span][span style=\"color:#0000BB\"]?>

[/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

[stewdawg35]

ok so I have changed it to the following:

 

Change your PHP to this:

 

<?php

echo $row['value'];

?>

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]    [span style=\"color:#0000BB\"]<?php

[/span][span style=\"color:#FF8000\"]// Establish DB connection

        [/span][span style=\"color:#0000BB\"]$host [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"localhost\"[/span][span style=\"color:#007700\"];

        [/span][span style=\"color:#0000BB\"]$user [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"user\"[/span][span style=\"color:#007700\"];

        [/span][span style=\"color:#0000BB\"]$pass [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"pass\"[/span][span style=\"color:#007700\"];

        [/span][span style=\"color:#0000BB\"]$dbname [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"db_name\"[/span][span style=\"color:#007700\"];

 

[/span][span style=\"color:#0000BB\"]$connection [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_connect[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$host[/span][span style=\"color:#007700\"],[/span][span style=\"color:#0000BB\"]$user[/span][span style=\"color:#007700\"],[/span][span style=\"color:#0000BB\"]$pass[/span][span style=\"color:#007700\"]) or die ([/span][span style=\"color:#0000BB\"]mysql_errno[/span][span style=\"color:#007700\"]().[/span][span style=\"color:#DD0000\"]\": \"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]mysql_error[/span][span style=\"color:#007700\"]().[/span][span style=\"color:#DD0000\"]\"<BR>\"[/span][span style=\"color:#007700\"]);

[/span][span style=\"color:#0000BB\"]mysql_select_db[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$dbname[/span][span style=\"color:#007700\"]);

 

        

[/span][span style=\"color:#FF8000\"]// Formulate query

        [/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]sprintf[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\"SELECT distinct xcart_extra_field_values.value FROM xcart_products, xcart_products_categories, xcart_extra_field_values WHERE xcart_products.productid=\'xcart_products_categories.productid\' AND xcart_products_categories.categoryid=\'314\' AND xcart_products.productid=\'xcart_extra_field_values.productid\' AND xcart_extra_field_values.fieldid=\'1\' ORDER BY xcart_extra_field_values.value LIMIT 0, 30\"[/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]mysql_real_escape_string[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"]));

 

[/span][span style=\"color:#FF8000\"]// Perform Query

[/span][span style=\"color:#0000BB\"]$result [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$query[/span][span style=\"color:#007700\"]);

 

[/span][span style=\"color:#FF8000\"]// Check result

// This shows the actual query sent to MySQL, and the error. Useful for debugging.

[/span][span style=\"color:#007700\"]if (![/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"]) {

   [/span][span style=\"color:#0000BB\"]$message  [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\'Invalid query: \' [/span][span style=\"color:#007700\"]. [/span][span style=\"color:#0000BB\"]mysql_error[/span][span style=\"color:#007700\"]() . [/span][span style=\"color:#DD0000\"]\"\n\"[/span][span style=\"color:#007700\"];

   [/span][span style=\"color:#0000BB\"]$message [/span][span style=\"color:#007700\"].= [/span][span style=\"color:#DD0000\"]\'Whole query: \' [/span][span style=\"color:#007700\"]. [/span][span style=\"color:#0000BB\"]$query[/span][span style=\"color:#007700\"];

   die([/span][span style=\"color:#0000BB\"]$message[/span][span style=\"color:#007700\"]);

   }

   

[/span][span style=\"color:#FF8000\"]// Use result

[/span][span style=\"color:#007700\"]while ([/span][span style=\"color:#0000BB\"]$row [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_fetch_assoc[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"])) {

    echo [/span][span style=\"color:#0000BB\"]$row[/span][span style=\"color:#007700\"][[/span][span style=\"color:#DD0000\"]\'value\'[/span][span style=\"color:#007700\"]];

    }

    

[/span][span style=\"color:#FF8000\"]// Free the resources associate with the result set

[/span][span style=\"color:#0000BB\"]mysql_free_result[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"]);

[/span][span style=\"color:#0000BB\"]?>[/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

 

Do I need to change the following?

 

mysql_real_escape_string($result)

 

//or this...

 

while ($row = mysql_fetch_assoc($result)) {

echo $row['value'];

}

 

 

Also could the problem be in the query? Are my quotations correct? Again thanks a million you guys are great!

[ryanlwh]

in the query, if you're matching column to column, don't use quote marks:

xcart_products.productid='xcart_products_categories.productid' <-- remove the quote

[stewdawg35]

okay I have made those changes but still I am getting no output..not even an error code. This is what I have thus far:

 

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

 

[span style=\"color:#0000BB\"]<?php

[/span][span style=\"color:#FF8000\"]// I excluded the DB connect info

// Formulate query

        [/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]sprintf[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\'SELECT distinct xcart_extra_field_values.value FROM xcart_products, xcart_products_categories, xcart_extra_field_values WHERE xcart_products.productid=xcart_products_categories.productid AND xcart_products_categories.categoryid=314 AND xcart_products.productid=xcart_extra_field_values.productid AND xcart_extra_field_values.fieldid=1 ORDER BY xcart_extra_field_values.value LIMIT 0, 30\'[/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]mysql_real_escape_string[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"]));

 

[/span][span style=\"color:#FF8000\"]// Perform Query

[/span][span style=\"color:#0000BB\"]$result [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$query[/span][span style=\"color:#007700\"]);

 

[/span][span style=\"color:#FF8000\"]// Check result

// This shows the actual query sent to MySQL, and the error. Useful for debugging.

[/span][span style=\"color:#007700\"]if (![/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"]) {

   [/span][span style=\"color:#0000BB\"]$message  [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\'Invalid query: \' [/span][span style=\"color:#007700\"]. [/span][span style=\"color:#0000BB\"]mysql_error[/span][span style=\"color:#007700\"]() . [/span][span style=\"color:#DD0000\"]\"\n\"[/span][span style=\"color:#007700\"];

   [/span][span style=\"color:#0000BB\"]$message [/span][span style=\"color:#007700\"].= [/span][span style=\"color:#DD0000\"]\'Whole query: \' [/span][span style=\"color:#007700\"]. [/span][span style=\"color:#0000BB\"]$query[/span][span style=\"color:#007700\"];

   die([/span][span style=\"color:#0000BB\"]$message[/span][span style=\"color:#007700\"]);

   }

   

[/span][span style=\"color:#FF8000\"]// Use result

[/span][span style=\"color:#007700\"]while ([/span][span style=\"color:#0000BB\"]$row [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_fetch_assoc[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"])) {

    echo [/span][span style=\"color:#0000BB\"]$row[/span][span style=\"color:#007700\"][[/span][span style=\"color:#DD0000\"]\'value\'[/span][span style=\"color:#007700\"]];

    }

    

[/span][span style=\"color:#FF8000\"]// Free the resources associate with the result set

[/span][span style=\"color:#0000BB\"]mysql_free_result[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$result[/span][span style=\"color:#007700\"]);

[/span][span style=\"color:#0000BB\"]?>[/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]