Jump to content


Photo

displaying a complex mysql query with php


  • Please log in to reply
5 replies to this topic

#1 stewdawg35

stewdawg35
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 November 2005 - 05:17 PM

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

#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 08 November 2005 - 05:41 PM

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.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 08 November 2005 - 05:43 PM

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--]
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#4 stewdawg35

stewdawg35
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 November 2005 - 05:51 PM

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? [code=php:0]

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!

#5 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 08 November 2005 - 08:13 PM

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

Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#6 stewdawg35

stewdawg35
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 November 2005 - 08:50 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users