mysql select question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dilbert
Forum Newbie
Posts: 10
Joined: Mon Mar 10, 2003 8:34 am

mysql select question

Post by dilbert »

Can I do below in just one line of code?
Let $array be array (A, B, C)

I want use select for below purpose

select (*) form tablw where id=A or B or C <= content in the $array

how can I get that, Thx a lot :D
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Could you explain a little more - are you trying to find out whether B or C is less than or equal to any of the contents of $array or all of the contents of array?

Mac
dilbert
Forum Newbie
Posts: 10
Joined: Mon Mar 10, 2003 8:34 am

Post by dilbert »

Sorry for my unclearness :P

in the previous post , <= is an arrow

in array , say $array , it contain 'A', 'B', and 'C', I know there is something like [select (*) from table where id = 'A' or id = 'B' or id = 'C'], but i don't want to break down my array for using 'for' loop, so I want to select something from the database where id match one of the string in the array , ie 'A', 'B', 'C' in the example to solve the problem.

I hope it will be more detailed.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Thanks for the clarification. To do what you are describing above you can use the IN operator, which is a way of simplifying statements like:

Code: Select all

SELECT field1, field2, field3 FROM table WHERE id='A' OR id='B' or id='C'
You can instead do something like the following:

Code: Select all

<?php
// assuming $array = array('A', 'B', 'C');
$sql_info = "'".implode("', '", $array)."'";
$sql = "SELECT field1, field2, field3 FROM table WHERE id IN($sql_info)";
The SQL statement will then look like:

Code: Select all

SELECT field1, field2, field3 FROM table WHERE id IN('A', 'B', 'C')
For more info see:
http://www.mysql.com/doc/en/Comparison_ ... ml#IDX1126

Mac
dilbert
Forum Newbie
Posts: 10
Joined: Mon Mar 10, 2003 8:34 am

Post by dilbert »

Thx for your quick reply :D
dilbert
Forum Newbie
Posts: 10
Joined: Mon Mar 10, 2003 8:34 am

Post by dilbert »

I have another question, I want to call the variables from the database as the array mention in above example.I use mysql_fetch_array() but I get error message like below

Warning: array_sum() [function.array-sum]: The argument should be an array in /home/dilbert/public_html/fish/shared/global.php on line 260
Warning: implode() [function.implode]: Bad arguments. in /home/dilbert/public_html/fish/shared/global.php on line 262
6

How can I solve this, it is wrong to use mysql_fetch_array()? Thx
dilbert
Forum Newbie
Posts: 10
Joined: Mon Mar 10, 2003 8:34 am

Post by dilbert »

that means I want to get array of the table of a field, eg, all 'userid' of the whold table of all rows

;)
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Could we see the code you are trying to use please.

Mac
dilbert
Forum Newbie
Posts: 10
Joined: Mon Mar 10, 2003 8:34 am

Post by dilbert »

$start_query = "select * from member where refer_by = '$user'";

for ($i = 1; $i <= 6;) {

$record=mysql_query($start_query);

$content=mysql_fetch_array($record_8);

$money += array_sum ($content_8[earning]); // i want $content_8[earning] is a array of all the fields in 'earnings'

$sql_info = "'".implode("', '", $content_8[refer_by])."'"; // i want $content_8[refer_by] is a array of all the fields in 'refer_by'

$start_query = "SELECT * FROM member WHERE username IN($sql_info)";

$i = $i + 1;

}
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

That code is, well, a bit messed up. I'm really not sure what you are trying to achieve with it:
  • This is not the best syntax for a for loop,

    Code: Select all

    for ($i = 1; $i <= 6; ) {
    check out the manual:
    http://www.php.net/manual/en/control-structures.for.php
  • Why are you using a for loop? Why are you looping 6 times?
  • You have this:

    Code: Select all

    $record=mysql_query($start_query);
    and then this:

    Code: Select all

    $content=mysql_fetch_array($record_8);
    Where does $record_8 come from?
  • Why are you running the query within the loop if it's not changing?
  • Where does $content_8 come from?
  • How can you get a list of different refer_by's if you are only selecting the records where refer_by = $user, because refer_by for each record will equal whatever you've set $user to be.
  • $content_8['refer_by'] is not an array, it is only one value so you can't implode it - mysql_fetch_array() only fetches one row of the result at a time so you need to manually add the values into an array before you can implode them.
Mac
dilbert
Forum Newbie
Posts: 10
Joined: Mon Mar 10, 2003 8:34 am

Post by dilbert »

oh, sorry, $record_8 should be $record

1. the query is changing since the $start_query is changing every loop
2. I am working on some downline system to count the money of different level. The chart is something like this:
Image
the 'refer_by' of 1 and 2 are A, 'refer_by' of 3 is B, 'refer_by' of 4, 5 and 6 is C, and so on.

So $i have to loop 6 times becasue I want my system consist of 6 downline level.
I know $content_8[refer_by] is not array , the only solution is to using another loop?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

$start_query is not changing for each loop because $user is not changing this also means that the value for refer_by is not changing.

Are you trying to calculate the earnings for all refer_by's? Are you trying to calculate the earnings for each refer_by? Are you trying to calculate the earnings for each user? Where does $user come from? What does the database structure look like?

Mac
dilbert
Forum Newbie
Posts: 10
Joined: Mon Mar 10, 2003 8:34 am

Post by dilbert »

the first line

$start_query = "select * from member where refer_by = '$user'";

is not include in the for loop, so it only run once

just a example, and I assume everything work fine

$user = 'red_dots'

then find in the table which refer_by = 'A' , and find that user 'A' 'B' "C' is refered by 'red_dots'

then new $start_query run the 2nd loop , and get the ppl refer by 'A' "B' and 'C' , they are 1 - 6, and so on

SELECT * FROM member WHERE username IN('A' 'B' C')

loop again
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

If $user = 'red_dots' then your first SQL statement looks like this:

Code: Select all

select * from member where refer_by = 'red_dots'
so when you query the database the ONLY result you will get for refer_by is red_dots because you've excluded everything else in that first SQL statement.

Mac
Post Reply