Is there a way to pull MySQL results based on a number which begins with..

[BadGoat]

... a specific searched bigit? For example, let's say that I want to display records which begin with the number 10.  I was hoping that it would be as simple as [code]SELECT record FROM table WHERE record_number LIKE '" . $_GET['number] . "%' [/code]but that doesn't seem to work. Is there a MySQL operator or function which will do this?

[.josh]

that should work. i notice you are missing a ' around 'number' in your $_GET['number']

also, you shouldn't directly insert a $_GET variable into a sql query.  leaves you open for sql injection attacks.

edit: well, show the actual code block that that's in.  i mean, technically, there are no beginning quotes around the query or nothing.  Since I already found 1 typo, show the whole thing.

[BadGoat]

Hi Crayon,

The missing ' was my own fault when making the variable names something easier to identify. In the actual query I have the '.  To complicate things a bit, the query is complex, where the 'record_number is actually company.record. Is it possible to have a complex query where one part is surrounded by a function, like '".escapeSingleQuotes('record')."' ? And how does it get complicated when it is part of a JOINED query?

[printf]

Something like...

[code]$query = "SELECT * FROM table WHERE CAST(SUBSTR(number, 1, 2) AS SIGNED) = " . intval ( $_GET['number] );[/code]


me!

[printf]

If you don't know what the number length will be add that logic to the query substr...

replace...

[code]$query = "SELECT * FROM table WHERE CAST(SUBSTR(number, 1, " . strlen ( intval ( $_GET['number] ) ) . ") AS SIGNED) = " . intval ( $_GET['number] );[/code]


me!

[BadGoat]

/scratches head for a bit...

printf, what if the value is not stored as an integer? As an example, I want to be able to find an IP address record where the IP address is stored as a string using ip2long. I'm trying this query[code]SELECT ips.id, ips.sip, ips.eip, ips.nic, ips.company_id, company.company_id, company.company, company.e1, ips.no_ips FROM ips, company WHERE ips.company_id = company.company_id  AND '".long2ip('sip')."' LIKE '" . $_GET['alpha'] . "%' ORDER BY sip ASC[/code] but there appears to be a logic problem in that it does not see a record associated to a number. I get an error of Warning: mysql_data_seek(): Offset 0 is invalid for MySQL result index 3 (or the query data is unbuffered)
which references this code[code]';
  mysql_data_seek($queryresult,0)  ;
while($row = mysql_fetch_array($queryresult)) {

    if ($tdcount == 1) echo "<tr>";
echo '[/code]

I'm certain I've overcomplicated it, but not sure where the problem lies.

[printf]

Quote MySQL Manual

If your using the PHP function ip2long or the MySQL function INET_ATON(), it is recommended that you use an INT UNSIGNED  column. If you use a (signed) INT column, values corresponding to IP addresses for which the first octet is greater than 127 cannot be stored correctly. Never use CHAR types to store dotted-quad representation of a network address if you need to CAST them, as in matching substrings within the ip!


Here is a few ways to do what you want!

1. change the column to unsigned INT

Then you can do this...

SELECT * FROM table WHERE SUBSTR(INET_NTOA(number), 1, 3) = '209';

This would select all rows, where the IP begins with (209)

Say you wanted to get a IP that starts with 10., not 100., then you need to include the [b]period[/b], so you only get IP(s) starting with 10 and not 10 and 100

examples!

// get ips starting with (4)

SELECT INET_NTOA(number) AS ip FROM table WHERE SUBSTR(INET_NTOA(number), 1, 2) = '4.';

// get ips starting with (10)

SELECT INET_NTOA(number) AS ip FROM table WHERE SUBSTR(INET_NTOA(number), 1, 3) = '10.';

// get ips starting with (100)

SELECT INET_NTOA(number) AS ip FROM table WHERE SUBSTR(INET_NTOA(number), 1, 3) = '100';

You can also use LIKE, seeing you are casting it back to a dotted ip!

// get ips starting with (10)

SELECT INET_NTOA(number) AS ip FROM table WHERE INET_NTOA(number) LIKE '10%';

// get ips starting with (127.0)

SELECT INET_NTOA(number) AS ip FROM table WHERE INET_NTOA(number) LIKE '127.0%';


me!

[BadGoat]

There we go, that's making some sense. Many thanks :)