Jump to content

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


BadGoat

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?
Link to comment
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.
Link to comment
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?
Link to comment
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!
Link to comment
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.
Link to comment
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!


Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.