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

[dagon]

WHERE YEAR(NOW())=YEAR(YOUR_DATE)

you need to add YOUR_DATE field

[neridaj]

I got the same thing i.e., the mysql monitor returns the correct count while the script returns 0.

mysql> select count(inv_number) from invoice where year(now()) = year(inv_date);
+-------------------+
| count(inv_number) |
+-------------------+
| 4 |
+-------------------+

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

[dagon]

$invnum = (int)$today+$count;

whats that suppose to be doing?

[neridaj]

casting the $today string to an int in order to add $count to it.

[Weedpacket]

neridaj wrote:

casting the $today string to an int in order to add $count to it.

PHP would do the cast automatically. But anyway, you wrote this:

	$today = date("Ymd").'00';

. So why not just write

$invnum = date("Ymd").$count;

instead? Either way, is there no possible risk of $count being more than 99?

the script returns 0.

Are you sure it's returning zero? What does $conn->query() actually return?

function get_invoice_number()
{
	$year = (int)date("Y");
	$today = date("Ymd").'00';
	$conn = db_connect();
	$count = $conn->query("select count(inv_number) from invoice where year(now()) = year(inv_date)");
var_dump($count); die;

[neridaj]

I kept getting an error when trying to implicitly cast.

If I don't concatenate the date("Ymd").'00', when the count is added it will just increment the day. If I just do $invnum = date("Ymd").$count; then the last two digits never increment i.e., 2009021100, 2009021101, etc.

$count is actually a mysqli_result which I've revised below, which still isn't working i.e., the query always returns 1 when run from the script.

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 = $today+$count;
		return $invnum;
}

[dagon]

$result 1 = query worked

but your not actully retrieving the value mysql returned

[neridaj]

So how do I get the actual value other than this? If "1" represents a successful query how do you explain the fact that even if I put "blah" in the query instead of a numerical value I still get "1"?

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 'blah'");
	if(!$result)
		return false;
	else
		// $invnum = $today+$count;
		// return $invnum;
		$count = $result->num_rows;
		var_dump($count);
}

outputs int(1)

[dagon]

becuause its still a valid query.

[Weedpacket]

dagon wrote:

becuause its still a valid query.

....and $result->num_rows tells you the number of rows that were returned (which is why it's called num_rows, I guess).

neridaj wrote:

I kept getting an error when trying to implicitly cast.

And the error says .... what? (PHP has no problem implicitly casting integers to strings.)

So how do I get the actual value other than this?

What does the manual say? I know ... let's find out and see (I don't use the MySQLi interface - I don't use MySQL - so this will be a learning experience for me.)

Hm... Vendor-specific database functions ... MySQL ... MySQLi ... ah, [man]MySQLi_Result[/man] class. Hm.... "Get current field offset blahblah" .... "Adjust the result pointer blah" .... oh, this looks good: "mysqli_result::fetch_array — Fetch a result row as an associative, a numeric array, or both". Let's have a look at that...

Lessee... Example 1 looks good. Here's an idea: have a crack at something like this:

$result_array = $result->fetch_array(MYSQLI_NUM);
$count = $result_array[0];