Check if invoice contains mobile phone

[vinpkl]

hi all

i have different categories like mobiles, cameras, gps etc.

then i have product_table in which products are added under those categories.

now i want to show a special coupon button on invoices which include mobile purchases by customers.

so if a invoice contains “mobile phone” then only that button should appear in front of invoice number.

i have

1) product_name in order_detail_table.

2) category_name "mobile phone" in product_table.

so its a huge database and i cannot change anything in it.

where i m stuck is how to check that invoice contains mobile phone.

$or_detail_qry="select * from order_detail_table where order_id = $ord_id";
$or_detail_result=mysql_query($or_detail_qry);
while($or_detail_row=mysql_fetch_array($or_detail_result))
{
	$product_name=$or_detail_row['product_name'];
}
			
			
$prod_qry="select * from product_table where category_name='Mobile Phones'";
$prod_result=mysql_query($prod_qry);
while($prod_row=mysql_fetch_array($prod_result))
{			
	$prod_name=$prod_row['product_name'];
}
			
									
if($product_name == $prod_name)
{
			
	echo "<td>". "<img src='images/special_coupon.gif' />" . "</td>";
}

the whole above code is inside a while loop which displays all the invoices result rows.

vineet

[Atli]

Hey.

Not 100% sure if I am understanding you correctly, but if I am, this may help you:

<?php
$ord_id = (int)$_GET['ord_id']; // Or whatever you do.

// Query for all the products in the order.
$sql = "SELECT  o.`product_name`,
                IFNULL(p.product_name, FALSE) AS `has_mobile`
        FROM    `order_detail_table` AS o
        LEFT JOIN `product_table` AS p
            ON  p.`category_name` = 'Mobile Phones'
            AND p.`product_name` =  o.`product_name`
        WHERE   o.`order_id` = $ord_id";
$result = mysql_query($sql) or die(mysql_error());

// Print the table header
echo '<table>';
echo '<tr><th>Product name</th><th>Coupon</th></tr>';

// Print each product
while($row = mysql_fetch_assoc($result))
{
    echo '<tr><td>'. $row['product_name'] .'</td>';
    
    // Check if there was a mobile, and print the image
    // if there was.
    if($row['has_mobile'] !== false) {
        echo '<td><img src="images/special_coupon.gif"></td>';
    }
    echo '</tr>';
}

// Close the table
echo '</table>';
?>

This, based on the two queries in your code, should fetch the name of every product in that order, and check if the product is a mobile phone. Then it simply prints a table, adding an image to the products that are mobile phones.

[vinpkl]

hi atli

this error it gives

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IFNULL(p.product_name, FALSE) AS has_mobile FROM order_detail_table AS ' at line 2

[Atli]

Ohh, ok. I forgot the comma after the first column. I’ve edited the code I posted earlier to fix that. Should work now

[vinpkl]

hi atli

the error is gone now but its showing button on all invoices.

its not checking whether it has mobile phone or not.

vineet

[Paul_Wilkins]

@vinpkl

its not checking whether it has mobile phone or not.

Atli has provided for you an extra column to the database results called has_mobile

vinpkl - you can use that extra column to adjust how your PHP code works.

[vinpkl]

hi pmw57

means i have to create an extra column in my database.

but in which table

can you clear it more

vineet

[Atli]

Odd. The problem is that MySQL is returning 0 as a string for FALSE values. I was sure it returned a boolean FALSE.

Anyhow, to fix it replace this line:

if($row['has_mobile'] !== false) {

with:

if($row['has_mobile'] != false) {

Basically, the “!==” operator strictly checks for a boolean false, while “!=” checks for any value that evaluates as false, like 0 or NULL.

When the product is a mobile, the ‘has_mobile’ field contains the product name, which is a string and thus evaluates as TRUE. If it is not a mobile, it returns a MySQL FALSE, which is returned to PHP as “0”, which PHP evaluates as false.

[Paul_Wilkins]

@vinpkl

means i have to create an extra column in my database.

but in which table

No, you don’t have to change your database.

This part of the SQL query creates the column called has_mobile
IFNULL(p.product_name, FALSE) AS has_mobile

The only part that needs to be corrected, is where the PHP code checks the result from that has_mobile field.

I suggest that you run the SQL query against your database, to investigate what that has_mobile has, along with the rest of your results.

As Ali says, use != FALSE instead of !== FALSE and you should see what you’re after.

[vinpkl]

hi atli

i tried myself also earlier removing one equal to sign. but got same result.

here is the echo result of sql query

SELECT o.product_name FROM order_detail_table AS o LEFT JOIN product_table AS p ON p.category_name = 'Mobile Phones' AND p.product_name = o.product_name WHERE o.order_id = 334

vineet

[Paul_Wilkins]

@vinpkl

i tried myself also earlier removing one equal to sign. but got same result.

What does $row look like for a mobile phone, and for one that is not a mobile phone?

[vinpkl]

i meant to say that if i use

!=

then no button is displayed in front of invoices.

otherwise

if i use

!==

then every invoice row displays the buttons

vineet

[Paul_Wilkins]

Stay with the !=

What does the category name look like for mobile phones?
Does this part need to be adjusted?

...
ON  p.`category_name` = 'Mobile Phones'
...

[vinpkl]

hi pmw57

the column name and category name both are correct in above statement

vineet

[Paul_Wilkins]

@vinpkl

the column name and category name both are correct in above statement

So, we need to find out what a result row looks like for a mobile phone, and for one that is not a mobile phone.

[vinpkl]

if i understood your point then

all rows with or without mobile phone are showing

1. order_id
2. order_date
3. order_total
4. button (which is blank at present)

vineet

[Paul_Wilkins]

@vinpkl

if i understood your point then

No, I’m afraid not.

What I want to see is the result of an invoice, where one of the items is a mobile phone.

SELECT
    o.`product_name`,
    IFNULL(p.product_name, FALSE) AS `has_mobile`
FROM `order_detail_table` AS o
    LEFT JOIN `product_table` AS p
        ON  p.`category_name` = 'Mobile Phones'
            AND p.`product_name` =  o.`product_name`
WHERE o.`order_id` = 31173

[Atli]

@vinpkl

hi atli

i tried myself also earlier removing one equal to sign. but got same result.

here is the echo result of sql query

SELECT o.product_name FROM order_detail_table AS o LEFT JOIN product_table AS p ON p.category_name = 'Mobile Phones' AND p.product_name = o.product_name WHERE o.order_id = 334

vineet

That is not the correct query. You are missing the ‘has_mobile’ column that the query I posted creates. If you leave it out, as you do there, then all the rows will be missing the image.

[vinpkl]

hi atli

i repasted your code and echo it as

SELECT o.`product_name`, IFNULL(p.product_name, FALSE) AS `has_mobile` FROM `order_detail_table` AS o LEFT JOIN `product_table` AS p ON p.`category_name` = 'Mobile Phones' AND p.`product_name` = o.`product_name` WHERE o.`order_id` = 334

vineet

[vinpkl]

hi pmw57

the invoice that contains mobile shows an extra blank column

but the invoice that doesnt contains mobile doesnt show extra column.

in both case the button is missing.

vineet