[SOLVED] SELECT *

[Maverickb7]

I was wondering if someone could help me. What would I have to put within the $sort variable to have that request display all rows starting with a number? right now I have to put 1,2,3,4,5,6,7,8,9 seperate. Anyway I can include it all in one variable that would work below?

 

$q = 'SELECT * '
        . ' FROM `company_details` '
        . ' WHERE `cname` LIKE  \'' . $sort . '%\''
        . ' ORDER BY `cname` ASC'; 
$r = mysql_query($q);

[PC Nerd]

look at string formatting

 

% means any char of any length.  look at other wildcards that do stuf with only 1 character, so that you can loop through the string......... its all in string formatting and conditions

 

 

good luck

[Psycho]

I believe this will work for you (not tested).

SELECT *
FROM company_details
WHERE SUBSTR(cname, 1, 1) IN (0,1,2,3,4,5,6,7,8,9)
ORDER BY `cname` ASC

[Maverickb7]

so I would use the following:

 

if ($sort = #) {
$q = 'SELECT * '
        . ' FROM `company_details` '
        . ' WHERE SUBSTR(cname, 1, 1) IN (0,1,2,3,4,5,6,7,8,9)'
        . ' ORDER BY `cname` ASC'
}
else {
$q = 'SELECT * '
        . ' FROM `company_details` '
        . ' WHERE `cname` LIKE  \'' . $sort . '%\''
        . ' ORDER BY `cname` ASC'; 
}

 

Basically what I'm doing is receiving information from the url. detail.php?sort=a <-- like that. With that it will list all results starting with A. I'm trying to have it display all numeric results if # is inputed where A was. Would this code do that for me.. if not how would I change it to work?

[Maverickb7]

Arg... I've been looking for a solution for hours now. Could someone give me a hand?

[Psycho]

Well, did you at least test if the SQL statement I posted gave you the results you wanted? Because determining wether the input is a number or not is a different question. So, which one are you asking now?

 

Assuming the query I gave you works, here is how you would determine if the input was a number:

 

<?php
if (is_numeric($sort)) {
   $q = 'SELECT * '
        . ' FROM `company_details` '
        . ' WHERE SUBSTR(cname, 1, 1) IN (0,1,2,3,4,5,6,7,8,9)'
        . ' ORDER BY `cname` ASC';
}
else {
   $q = 'SELECT * '
        . ' FROM `company_details` '
        . ' WHERE `cname` LIKE  \'' . $sort . '%\''
        . ' ORDER BY `cname` ASC'; 
}
?>

[Maverickb7]

doesn't work. I used the code you provided and it doesn't change anything. I put a number as $sort and it shows me every item in the table. Any ideas?

[Psycho]

OK, the numbers just need to be enclosed in quotes. I have tested this and it works:

 

Made some minor changes to simplify as well

<?php

if (is_numeric($sort)) {
  $WHERE = "WHERE SUBSTR(cname, 1, 1) IN ('0','1','2','3','4','5','6','7','8','9')";
} else {
  $WHERE = "WHERE cname LIKE '$sort%'";
}

$q = "SELECT *
      FROM `company_details`
      $WHERE
      ORDER BY `cname` ASC";

?>

[Maverickb7]

Works beautiful mjdamato, thanks so much!

 

Just curious... it's not importent but how come this doesn't work?

if ($sort == "#") {
  $WHERE = "WHERE SUBSTR(cname, 1, 1) IN ('0','1','2','3','4','5','6','7','8','9')";
} else {
  $WHERE = "WHERE cname LIKE '$sort%'";
}

$q = "SELECT *
      FROM `company_details`
      $WHERE
      ORDER BY `cname` ASC";

 

I'm trying to get it to allow me to use detail.php?sort=# and have it display all the numeric results but that doesn't seems to work. Any ideas?

[Psycho]

Some characters cannot be sent via the query string in an unencoded format. You have probably seen plenty of web addresses that have %20 in them. That is the URL encoding for a space. Similarly the pound character needs to be encoded if passed on the query string.

 

So you would need to use detail.php?sort=%23 and then your PHP code would recognize it as the pound symbol - #