[Resolved] retrieving "specific" data from a field

Barb54

Hi, it's been a while since I posted for help. Finally learning this stuff, lol.

I want to connect to a table and retrieve data that meets a specific criteria. I can get it with only 1 so far. I have tried several things and nothing has the desired results yet. Of course, I included my database connect info at top of file.

Example code that works:

$query="SELECT `name` , `gameID` , `codeID` , SUM( `codeAmt` ) AS `Totals` 
FROM `dataTable` WHERE `gameID` 
LIKE 'game' AND `codeID` LIKE 'S1'
//the LIKE is where I want to pull out S1, S2, and S3 instead of just S1 only
GROUP BY `name`
ORDER BY `Totals` DESC ";

$result=mysql_query($query);
$num=mysql_numrows($result);

if (!$num){ 
   echo "<br>";
   echo "<p align=center>Ooops, no data in this category yet.</font></p>";
}

mysql_close();
?>
<? include ("header.php"); ?>
<!-- display the page -->
<DIV>
<p align=center> Game STATS Category "S" </p><br>
<center>
<table width=50%>
<tr>
<th align=left><u><b>Rank</b></u></th>
<th align=left><u><b>Name</b></u></th>
<th align=right><u><b>Totals</b></u></th>
</tr>

<?php 
$i=0;
while ($i < $num)   

{  

$rank = $i+1; 
$nick=mysql_result($result,$i,"name"); 
$type=mysql_result($result,$i,"codeID"); 
$Totals=mysql_result($result,$i,"Totals");  

echo "<tr>";  

echo "<td align=left>$rank</td>"; 
echo "<td align=left>$name</td>";
echo "<td align=right>$Totals</td>";  

echo "</tr>"; 

++$i;  

}
?>

I know it's probably something very simple but I am still a newby as far as I am concerned and my searches on this site came up empty on how to do this.

Any help is greatly appreciated.
Thanks,
Barb

bad76

Hi,
LIKE has some usefull wildcard character:

% - Matches any number of characters, even zero characters

_ - Matches exactly one character

So in your code you can use:

WHERE ... codeID LIKE 'S%'

bye bye

dp777

If I understand your problem correctly, I believe the issue you are having is in your LIKE satements:

LIKE 'game' AND codeID LIKE 'S1'

if you want to get S1, S2, S3, etc it should be codeID LIKE 'S%'

where the % is a wild card which will get everything that starts with a 'S' samething with game, you need a wild card in there otherwise it is just like saying where = 'game' HTH!!

Barb54

Hi,
Thanks for your responses. I wish it was that simple, lol. The problem is that I have around 75 different codes. I can pull some out by using their category ID(another field in the table) for other stats pages. But for these particular ones, there could be an ST....whatever or an SR.... etc or an ST6 and I don't want to pick those codes up in my totals for that stats page.

I want to "isolate" these 3 codes only and not be worried that it's picking up totals from some other code. Does that make since? I tried my best to set up these codes so they would make since to future script users but in the process I think it made it harder to do things like this, lol.

I patiently await your response and thanks for your help in advance.

Barb

bad76

... and codeID NOT LIKE 'ST%' ...

May be a solution.

Or if S1, S2, S3 is up to S9 but not S10, you can use and codeID LIKE 'S_'

But if possible the rigth way is to rewrite this code so any code isn't the start for any other code.

For example adding a starting letter:
AS1, AS2, AS3, ...
BST1, BST2, BST3, ...
CSR1, CSR2, CSR3, ...

Or adding a char after the S:
SS1, SS2, SS3, ...
ST1, ST2, ST3, ...
SR1, SR2, SR3, ...

Or if the code are just three don't use the like:

and (codeID= "S1" OR codeID= "S2" OR codeID= "S3")

I hope this helps you.

Barb54

Hi again,
This is just to make it clearer if I didn't before:

I want it pull out:

codeID = 'S1' AND codeID= 'S2' AND codeID = 'S3'

For some reason the code doesn't work no matter what I've tried. I have tried braces, brackets, you name it, lol. I guess it either cannot be done or I'm not getting the syntax correct.

Thanks again,
Barb 😕

mtimdog

or
codeID in {'S1', 'S2', 'S3'}
might work.

bad76

Originally posted by Barb54
codeID = 'S1' AND codeID= 'S2' AND codeID = 'S3'

Is "OR" not "AND". There isn't any field with codeID=s1 and s2 and s3 at the same time...

Barb54

ahhhhhhh Bad76, I don't think I used parenthesis come to think of it.....................DUH! LOL I will give that a shot.

Dog, I have never used "in" before. Is there a link for help on understanding it's usage?

I will give both of these solutions a try and see what comes up. If I get the desired reslults I will mark this post resolved. If not, I'll be back, lol.

Thanks a heap,
Barb

bad76

You can retrieve some usefull information on "IN" here (in the middle of the page...) but this command it's supported only from mysql 4.1 , if I'm not bad...

bye bye

Sxooter

Two points:

1: MySQL supports regular expressions. regex is your friend. Harder to write at first, but infinitely more powerful than simple like comparisons:

http://dev.mysql.com/doc/mysql/en/Regexp.html

2: In(2,3,4,'uncle steve') has been supported for quite some time. in(select ya from da where pa=ma) has not.

Barb54

I couldn't get any of it to work as expected so I'm thinking I didn't make it clear enough.

I solved the problem by inserting 1 more column to use to pick up specific data types. That WORKED! lol I had to put that column in the category table as well so I could get it put in as a hidden value on insert forms.

So now I look for amt_type "A" or B, C, D etc and used phpmyadmin to update existing data which worked nicely with the sql area. Now I get the results I wanted, yesssss, lol.

Sometimes the answer is to adjust your db structure to work with what you are trying to do. Thanks for that suggestion. I had way too many codes already set up and in use to start changing them so a new field seemed the most logical solution.

We can consider this resolved. Thanks for all your help!
BarB