Jump to content


Photo

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


  • Please log in to reply
7 replies to this topic

#1 BadGoat

BadGoat
  • Members
  • PipPipPip
  • Advanced Member
  • 72 posts

Posted 03 October 2006 - 06:47 PM

... 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
SELECT record FROM table WHERE record_number LIKE '" . $_GET['number] . "%'
but that doesn't seem to work. Is there a MySQL operator or function which will do this?

#2 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 03 October 2006 - 06:51 PM

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.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#3 BadGoat

BadGoat
  • Members
  • PipPipPip
  • Advanced Member
  • 72 posts

Posted 03 October 2006 - 07:00 PM

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?

#4 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 03 October 2006 - 07:00 PM

Something like...

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


me!

#5 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 03 October 2006 - 07:08 PM

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

replace...

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


me!

#6 BadGoat

BadGoat
  • Members
  • PipPipPip
  • Advanced Member
  • 72 posts

Posted 03 October 2006 - 07:18 PM

/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
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
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
';
   mysql_data_seek($queryresult,0)  ;
while($row = mysql_fetch_array($queryresult)) {

    if ($tdcount == 1) echo "<tr>";
echo '

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

#7 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 03 October 2006 - 08:19 PM

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 period, 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!




#8 BadGoat

BadGoat
  • Members
  • PipPipPip
  • Advanced Member
  • 72 posts

Posted 03 October 2006 - 08:22 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users