Jump to content

Archived

This topic is now archived and is closed to further replies.

stewdawg35

displaying a complex mysql query with php

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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--]

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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--]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.