counting results from an sql query

[blackwidow]

Hi all,

I have a function that currently working. It displays a set of results based on criteria etc. I've been trying to count the number of results returned, but seem to be hitting a brick wall. here is my code:

function listItemsInvoice($txn_id_i)
{

// Performing SQL query
$txn_id_i = mysql_real_escape_string($txn_id_i);

$query = "select * from ipn_trans LEFT JOIN ipn_item ON ipn_trans.id=ipn_item.parent_id where ipn_trans.txn_id='$txn_id_i' ORDER BY ipn_trans.time DESC;";
$result = mysql_query($query) or die('Query failed: ' . $query . ' '. mysql_error());

if (!$result) {
	//echo "ERR";
	return;
}
$invoice = '';
$processed = array($txn_id_i => 'new');

if (mysql_num_rows($result) != 0) {
	while ($row = mysql_fetch_assoc($result)) {
		if ($processed[$txn_id_i] != $row['payment_status']) {
			if ($processed[$txn_id_i] == 'new')
				$processed[$txn_id_i] = $row['payment_status'];
			else
				continue;
				$item_number = $row['item_number'];
				$item_name = $row['item_name'];
				$type = $row['option_selection1'];
				$finish = $row['option_selection2'];
				$quantity = $row['quantity'];
				$net = $row['mc_gross'];
		}
		$invoice .=   "<tr>
    <td width=\"100\" align=\"left\" style=\"font:Arial; font-size:12px; color:#000000; padding-bottom:5px;\">($r)$item_number</td>
    <td width=\"350\" align=\"left\" style=\"font:Arial; font-size:12px; color:#000000; padding-bottom:5px;\">$item_name ($type | $finish) </td>
    <td width=\"100\" align=\"center\" style=\"font:Arial; font-size:12px; color:#000000; padding-bottom:5px;\">$quantity</td>
    <td width=\"100\" align=\"right\" style=\"font:Arial; font-size:12px; color:#000000; padding-bottom:5px; padding-right:10px;\">£$net</td>
  </tr>";
		}
}

mysql_free_result($result);
return $invoice;
};

I'd really appreciate some help with this as its been driving me mad!! 🙂

cheers

Mark

[bradmasterx]

mysqli_num_rows() function.

[_uk_stuff]

You already test to make sure that you have at least one record returned from your query.

So in theory you could just expand the test out a little i.e.

You currently have

 if (mysql_num_rows($result) != 0) { 

if you changed this to be..

$numRows = mysql_num_rows($result);
if ($numRows != 0) {

You could then refer to the number of records returned by echoing out the $numRows variable.

Hope it helps

[blackwidow]

Thanks Bradmasterx, that worked a treat, although I used mysql_num_rows as mysqli_num_rows threw up some errors. I must admit to not being too hot on the mysql side if things.

I need some sort of 'if' statement inside the function that basically says 'if there is one row echo "mytext", if there is two rows echo "mytext" twice'
etc etc, and to cap it at ten rows.

[blackwidow]

Hi [uk] stuff,

Thanks for your reply. here is my current extract:

$query = "select * from ipn_trans LEFT JOIN ipn_item ON ipn_trans.id=ipn_item.parent_id where ipn_trans.txn_id='$txn_id_i' ORDER BY ipn_trans.time DESC;";
	$result = mysql_query($query) or die('Query failed: ' . $query . ' '. mysql_error());

$row_cnt = mysql_num_rows($result);

[blackwidow]

[uk]stuff,

I've used your method, and its just as good as bradmasterx's. I'm still stuck on the 'if' statement though!

any help greatly appreciated!

Mark

[_uk_stuff]

All you need to do is place a loop after your new SQL call i.e.

$query = "select * from ipn_trans LEFT JOIN ipn_item ON ipn_trans.id=ipn_item.parent_id where ipn_trans.txn_id='$txn_id_i' ORDER BY ipn_trans.time DESC;";

# Ask the Database the question
$result = mysql_query($query) or die('Query failed: ' . $query . ' '. mysql_error());

# See how many rows are returned
$row_cnt = mysql_num_rows($result);

# Only echo out the detail if you recieved at least one record
if ( $row_cnt > 0 )
{
    # Loop through the returned rows and echo each out in turn.
    while ( $record = mysql_fetch_array($result) ) 
    {
        echo out the record here....
    }
}

Does this help at all?

[blackwidow]

Thanks for your swift replies guys. I've got it sorted now. Basically its part of an invoice template that I'm working on, and I always want the 'total' section to be displayed at the bottom of an A4 page as I think it looks professional.

What I was trying to do was effectively pad the bit between the list of items purchased and the 'total' section with blank rows according to how many items were bought so it kind of looked like a footer.

Once again, many thanks. If anyone wants a copy of the template let me know.

kind regards

mark