MYSQL queries

[meghatronic]

I have an incredibly simple database set up at the moment with data such as:

1 Tom
2 Dick
3 Harry
4 Hippo
5 Charlie

(literally the two columns)

At the moment I'm using:

$query="SELECT * FROM $type WHERE num='$num'";

Where "num" is the number passed via post. I'm just echoing number = name.

for example, blah.php?num=1 would yield "1 = Tom".

This works dandy, but how would I treat it if I passed: "1,2,5" to output:

1 = Tom
2 = Dick
5 = Charlie

I appreciate this is extremely simple and duly apologise!

 

[oneilldo]

you can use the keyword IN
SELECT * FROM TableName WHERE num IN(x,y,z)
hope this helps. You could also order the output by num or name if you wanted i.e.

SELECT * FROM TableName WHERE num IN(x,y,z)
ORDER BY num DESC

 

[meghatronic]

you can use the keyword IN
SELECT * FROM TableName WHERE num IN(x,y,z)
hope this helps. You could also order the output by num or name if you wanted i.e.

SELECT * FROM TableName WHERE num IN(x,y,z)
ORDER BY num DESC

IN IN IN! Got it. Cheers....

I knew it was simple. My google skills are failing...

 

[meghatronic]

Right, I'm now using:

$vars="'GO:0000001','GO:0000002','GO:0000003'";
$query="SELECT * FROM `db` WHERE `type` ='$type' AND `is_obsolete` = 0 AND `acc` IN($vars)";

However, my search input wouldn't be 'GO:0000001','GO:0000002','GO:0000003' ... It would be 0000001{tab}0000002{tab}0000003 etc..... Is there a quicky and easy way to prefix 'GO: to the first number, replace the tabs with ','GO: and then close with '

Did that make any sense? :-/

 

[LazyManc]

Try something like this:

// & = pass by reference, i.e. make changes to the
// current array rather than a copy
foreach ($vars as &$var){
  $var = 'GO:'.trim($var);
}

and then for your query:

// make a usable string out of the array, separated by ','
$vars = implode("','",$vars);

// dont forget the single-quotes in the IN
$Query = SELECT * 
             FROM `db` 
             WHERE `type` ='$type' 
             AND `is_obsolete` = 0 AND `acc` IN('$vars')";

*edit* - got the implode params the wrong way round, should work now

 

[meghatronic]

Cheers! Have sorted it now and used str replace to use commas, tabs and/or spaces.

Ta!

Quick question, what does the 'trim' do there? As I havne't used it on my version... :S

 

[toString]

Trim removes any whitespace from before or after the string.

 

[meghatronic]

If the 'number' is present more than once, it naturally only fetches the row once.

Is there a way to make a note of that? I.e. counting the frequency of the terms...?