Jump to content

Archived

This topic is now archived and is closed to further replies.

BadGoat

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

Recommended Posts

... 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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
Something like...

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


me!

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
/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.

Share this post


Link to post
Share on other sites
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!


Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.