[SOLVED] MySQL 4 to MySQL 5 ???

[davey_b_]

I'm trying to get this to work in MySQL 5 - I know my issue is to do with mixing comma and JOIN but I'm not sure how to solve it - can someone please help

<?php // just for code colours
SELECT o.*, u.user_name, u.email,s.order_status,od.product_discount, cm.nexchange_price,vcurrency_name,
		  sum((od.product_price-(od.product_price*product_discount/100)) * od.product_quantity) AS total 
	FROM ".$tableprefix."orders o,".$tableprefix."currency_master cm
	INNER JOIN ".$tableprefix."users u ON o.user_id = u.user_id
	INNER JOIN ".$tableprefix."order_status s ON o.order_status = s.order_status_id
	INNER JOIN ".$tableprefix."order_details od ON o.order_id = od.order_id
	WHERE od.artist_id = '".addslashes($artistid)."' 
	 AND o.vorder_currency = cm.vcurrency_code
	 AND o.order_id = '".addslashes($orderid)."'
	" . $qryopt . " GROUP BY o.order_id ORDER BY o.order_date DESC    ";

 

I tried this but it just creates a load of errors - I'm a bit lost...

<?php 
SELECT o.*, u.user_name, u.email,s.order_status,od.product_discount, cm.nexchange_price,vcurrency_name,
		  sum((od.product_price-(od.product_price*product_discount/100)) * od.product_quantity) AS total 
FROM ".$tableprefix."orders o
INNER JOIN ".$tableprefix."currency_master cm ON ( o.vorder_currency = cm.vcurrency_code ) 
INNER JOIN ".$tableprefix."users u ON ( o.user_id = u.user_id ) 
INNER JOIN ".$tableprefix."order_status s ON ( o.order_status = s.order_status_id )
INNER JOIN ".$tableprefix."order_details od ON ( o.order_id = od.order_id ) 
WHERE od.artist_id = '".addslashes($artistid)."' 
AND o.order_id = '".addslashes($orderid)."'
".$qryopt." GROUP BY o.order_id ORDER BY o.order_date DESC    ";

[rhodesa]

Can you post the errors you are getting?

[davey_b_]

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www.......

 

this is my sql string echoed...

SELECT o.*, u.user_name, u.email,s.order_status,od.product_discount, cm.nexchange_price,vcurrency_name, sum((od.product_price-(od.product_price*product_discount/100)) * od.product_quantity) AS total FROM tug_orders o,tug_currency_master cm INNER JOIN tug_users u ON o.user_id = u.user_id INNER JOIN tug_order_status s ON o.order_status = s.order_status_id INNER JOIN tug_order_details od ON o.order_id = od.order_id WHERE od.artist_id = '1' AND o.vorder_currency = cm.vcurrency_code AND o.order_id = '1020157' GROUP BY o.order_id ORDER BY o.order_date DESC

 

[rhodesa]

Right, but what is the error info from mysql_error()? You're code should look something like this:

 

<?php
//..all the connect to db stuff
$sql = "...that really long SQL...";
$result = mysql_query($sql)
  or die("Query Error: ".mysql_error());
$num = mysql_num_rows($result); //This fails
//..etc
?>

 

Add that mysql_error() line and post what it outputs

[davey_b_]

got ya,

 

Query Error: Unknown column 'o.user_id' in 'on clause'

[rhodesa]

Hum, yeah, it's gotta be the mix of the comma and joins. What are the errors in the second query where it's all JOINs? Or try using all commas:

 

<?php
"SELECT o.*, u.user_name, u.email,s.order_status,od.product_discount, cm.nexchange_price,vcurrency_name,
  sum((od.product_price-(od.product_price*product_discount/100)) * od.product_quantity) AS total 
FROM {$tableprefix}orders o,
{$tableprefix}currency_master cm,
{$tableprefix}users u,
{$tableprefix}order_status s,
{$tableprefix}order_details od
WHERE o.user_id = u.user_id AND
o.order_status = s.order_status_id AND
o.order_id = od.order_id AND
od.artist_id = '".mysql_real_escape_string($artistid)."' AND
o.vorder_currency = cm.vcurrency_code AND
o.order_id = '".mysql_real_escape_string($orderid)."'"
. $qryopt . " GROUP BY o.order_id ORDER BY o.order_date DESC";
?>

[davey_b_]

With that code I'm getting, same as with my alternative code.....

 

Warning: Division by zero in /var/www/httpdocs/mmviii/artists/vieworder.php on line 167

 

Warning: explode(): Empty delimiter. in /var/www/httpdocs/mmviii/includes/functions.php on line 1446

 

Warning: explode(): Empty delimiter. in /var/www/httpdocs/mmviii/includes/functions.php on line 1447

 

Warning: array_values(): The argument should be an array in /var/www/httpdocs/mmviii/includes/functions.php on line 1432

 

Warning: array_values(): The argument should be an array in /var/www/httpdocs/mmviii/includes/functions.php on line 1433

 

Warning: in_array(): Wrong datatype for second argument in /var/www/httpdocs/mmviii/includes/functions.php on line 1449

 

Warning: in_array(): Wrong datatype for second argument in /var/www/httpdocs/mmviii/includes/functions.php on line 1451

 

The relivent lines in functions.php

<?php
function dateFormat($input_date, $input_format, $output_format) //Line 1442
{
    preg_match("/^([\w]*)/i", $input_date, $regs);
    $sep = substr($input_date, strlen($regs[0]), 1);
    $label = explode($sep, $input_format);
    $value = explode($sep, $input_date);
    $array_date = array_combine($label, $value);
    if (in_array('Y', $label)) {
        $year = $array_date['Y'];
    } elseif (in_array('y', $label)) {
        $year = $year = $array_date['y'];
    } else {
        return false;
    }

    $output_date = date($output_format, mktime(0, 0, 0, $array_date['m'], $array_date['d'], $year));
    return $output_date;
}

[rhodesa]

Oh, well that is completely different...let's start with line 167 though

[davey_b_]

OK, line 167 from vieworder.php is marked below...

 

<?php
$result = mysql_query($sql)or die("Query Error: ".mysql_error());
while(mysql_num_rows($result)!=0){
$row = mysql_fetch_array($result);
$ordernumber = $row["order_id"];
$username = $row["user_name"];

$ordertotal = ($row["total"]/$row["nexchange_price"]); // Line 167
$orderstatus = getArtistOrderStatus($orderid, $artistid);
$orderdate = $row["order_date"];
$orderdate = dateFormat($orderdate,"Y-m-d","d-M-Y");
$userid = $row["user_id"];
}

[rhodesa]

First, that will throw an infinite loop. So, it's probably going through all the records, then after it's done, continuing with empty values. Replace that code with this:

 

<?php
$result = mysql_query($sql)or die("Query Error: ".mysql_error());
while($row = mysql_fetch_assoc($result)){
$ordernumber = $row["order_id"];
$username = $row["user_name"];

$ordertotal = ($row["total"]/$row["nexchange_price"]); // Line 167
$orderstatus = getArtistOrderStatus($orderid, $artistid);
$orderdate = $row["order_date"];
$orderdate = dateFormat($orderdate,"Y-m-d","d-M-Y");
$userid = $row["user_id"];
}

[davey_b_]

ok - that got rid of the loop and appears to have sorted the original issue but now I'm getting "Warning: Division by zero" message... is it related or a seperate issue??

 

<?php
										$sql=" SELECT od.*, s.order_status  FROM ".$tableprefix."order_details od
												INNER JOIN ".$tableprefix."order_status s ON s.order_status_id = od.item_status
												WHERE od.artist_id = '".addslashes($artistid)."' and od.order_id = '".addslashes($orderid)."'
												" . $qryopt . "  ";

										$result1 = mysql_query($sql) or die("Query Error: ".mysql_error());
										$i=$begin+1;
										while($rw = mysql_fetch_array($result1)){
											$check = "<input type='checkbox' class='textbox' name='chkitems[]' value='" . $rw["order_detail_id"] . "' > ";
											$rate = $rw["product_price"];
											$qty = $rw["product_quantity"];

											$price = $rate * $qty;
											$discountstr="";
							$ratestr=$rate;
							if($rw["product_discount"] !="0"){
							$rate2=$rate- ($rate*$rw["product_discount"])/100;
							$ratestr="<font color=red size=1><del>" .$currency_attributes[0]. "" .$rate. "</del></font><br>" .$currency_attributes[0]. "".number_format(($rate2),2 ,".","" );
								$price=$price-($price*$rw["product_discount"]/100);
								$discountstr="<br>Discount (".$rw["product_discount"]."%)";
							}

							$subtotal += $price + $postagetotal;
											$rate  = number_format($rate,2,".","" );
											$price = number_format($price,2,".","" );
										?>
										<tr>
											<td valign=top align="left"><? echo $i; ?></td>
											<td valign=top align="left"><? echo htmlentities($rw["product_name"]).$discountstr; ?></td>
											<td valign=top align="left"><? echo htmlentities($rw["product_code"]); ?></td>
											<td valign=top align="left"><? echo htmlentities($rate/$row["nexchange_price"]); ?></td> //HERE
											<td valign=top align="left"><? echo htmlentities($qty); ?></td>
											<td valign=top align="left"><? echo htmlentities($price/$row["nexchange_price"]); ?></td> //HERE
											<td valign=top align="left"><? echo htmlentities($rw["order_status"]); ?> </td>
										</tr>

[rhodesa]

It just means the nexchange_price column is 0 or NULL. You'll need to check your tables and SQL for that.

[davey_b_]

Yeah thought as much - thank you so much for your help