[SOLVED] making a like query on an int

[neridaj]

Hello,

 

I'm having problems returning the correct number of records from a column. When I run the statement in the mysql monitor I get the correct number of records returned but when the query is run from within my script it is returning the wrong amount i.e., 1. I tried using = instead of like but I need to use a wildcard % and I'm not sure I can use that on an int.

 

I need the count of how many records are in the table that begin with the current year,  the records are in the form of YYYYMMDDNN, where NN is the count returned from running the query added to the date("Ymd"). Do I need to change the column type to string to accurately use %?

 

function get_invoice_number()
{
$year = (int)date("Y");
$today = (int)date("Ymd").'00';
$conn = db_connect();
$count = $conn->query("select count(inv_number) from invoice where inv_number like '$year%'");
if(!$count)
	return false;
else
	$invnum = $today+$count;
	return $invnum;
}

 

Thanks for any help,

 

Jason

[rhodesa]

try:

SELECT COUNT(inv_number) FROM invoice WHERE CAST(inv_number AS CHAR) LIKE '$year%'

[neridaj]

Thanks, but no luck. No matter which sql queries work in the mysql monitor they all return 1 when run from the script, which is incorrect as there are 3 records in the table that begin with $year i.e., 2009. Here's my script with your query, which returns 2009021101 where the format is YYYYMMDDNN and NN represents the count.

 

function get_invoice_number()
{
$year = date("Y");
$today = date("Ymd").'00';
$conn = db_connect();
$result = $conn->query("SELECT COUNT(inv_number) FROM invoice WHERE CAST(inv_number AS CHAR) LIKE '$year%'");
$count = $result->num_rows;
if(!$count)
	return false;
else
	$invnum = (int)$today+$count;
	return $invnum;
}

 

Here is what I have in the table:

 

mysql> select * from invoice;

+------------+----------+-------------+--------------+-----------------+------------------+------------+

| inv_number | username | inv_amt_due | inv_amt_paid | inv_amt_balance | inv_description  | inv_date  |

+------------+----------+-------------+--------------+-----------------+------------------+------------+

| 2009021000 | testuser |      300.00 |        0.00 |          300.00 | test description | 2009-02-09 |

| 2009021004 | jcorky  |      300.00 |        0.00 |            0.00 | asdf            | 2009-02-10 |

| 2009021001 | hblaftin  |      300.00 |        0.00 |            0.00 | asdf            | 2009-02-10 |

+------------+----------+-------------+--------------+-----------------+------------------+------------+

3 rows in set (0.01 sec)

 

Thanks for the help,

 

J

[corbin]

Uhhh.... is inv_number numeric?  If so, couldn't you just do >= 2009000000?

[neridaj]

Yeah, I tried that and just get the same old int(1) when I var_dump. Anyways, the invoice number would be incorrect for any year beyond the current.

[corbin]

So wait....  Why are you doing it on inv_number and not inv_date?

[aschk]

So basically you're looking to generate an invoice number based on current date + XX.

Thus you need to count the number of invoices that have a date of today, and append 1 to that count.

 

...
$query = "SELECT CONCAT(REPLACE(CURRENT_DATE,'-',''),COUNT(*)+1) 
           FROM invoice 
           WHERE inv_date = CURRENT_DATE";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
$count = $row['inv_num'];

[neridaj]

I just had to tweak what you had a little bit but now it works, thanks a lot.