Using mysql in php

[acoolguy]

My table is set up like this

Field Type
name varchar(50)
setting varchar(50)
userid int(11)
value text

Now im trying to get the userid from the database where the name = name and setting = month the value = 6 and the setting = day and the value = 7

in the field "setting" there are various data name 2 of them are called day and month and basically im trying to get the userid number from the database where day = today and month = this month ive tried this and a few other ways but they dont work :(

$day = date("j");
$month = date("n");
$sql = "(SELECT userid FROM " .tablename. " WHERE name = 'addon' AND setting = 'day' AND value = $day) UNION (SELECT userid FROM " .tablename. " WHERE name = 'addon' AND setting = 'month' AND value = $month)";
$result = db_query($sql);
$row = db_fetch_assoc($result);
 

Any help please?

[TheServant]

Welcome to Bytes.
Do you get any errors?

tablename should be $tablename, I imagine. Fix that and see how it works.

[acoolguy]

@TheServant
Well thats just it the above code sort of works but it only picks one when there are more than one that meet the the search.

[TheServant]

@acoolguy
So that was just a typo with tablename instead of $tablename?

From what you have given, I can't see any other errors, so can you post how you're processing your result to display it?

Also, can you try and run two queries:

$sql_1 = "SELECT userid FROM " .$tablename. " WHERE name = 'addon' AND setting = 'day' AND value = $day";
$sql_2 = "SELECT userid FROM " .$tablename. " WHERE name = 'addon' AND setting = 'month' AND value = $month";
 
$result_1 = db_query($sql_1);
$result_2 = db_query($sql_2);
$row_1 = db_fetch_assoc($result_1);
$row_2 = db_fetch_assoc($result_2);
 

If they both work as expected, then I don't know. If they fail, or get the same error as when you use the UNION, then it's cleary your MySQL statement or database structure.

I usually use mysql_fetch_array(), so I am not sure of particular differences between that and db_fetch_assoc(), however, if you are generating more than one row of results, you will need to retrieve them with a while loop:

while($row = mysql_fetch_array($result)){
    echo $row['userid'];
}

[acoolguy]

@TheServant
Thanks for the reply however using the 2 sql statements it only shows 1 result yet i know there are more than 1 as i checked the database myself :(
I use db_num_rows($row) to get the number of rows thats returned from the query and it shows only 1 result yet there are more than 1.

[acoolguy]

@acoolguy
Update:

After going to the phpmyadmin and running the follow code in the sql section i get the result of 4 however using the same code in php i get 1 result ?

SELECT userid
FROM `tablename`
WHERE name = 'addon'
AND setting = 'day'
AND value = '7'
UNION SELECT userid
FROM `tablename`
WHERE name = 'addon'
AND setting = 'month'
AND value = '7'

[TheServant]

So if you do:

$sql_1 = "SELECT userid FROM " .$tablename. " WHERE name = 'addon' AND setting = 'day' AND value = $day"; 
$sql_2 = "SELECT userid FROM " .$tablename. " WHERE name = 'addon' AND setting = 'month' AND value = $month";   
$result_1 = db_query($sql_1); 
$result_2 = db_query($sql_2); 
echo db_num_rows($result_1);
echo db_num_rows($result_2);
 

The first one returns the correct number and the second always returns 1, and there should be more than 1 for the second?

The other thing to test is remove your variables and try run EXACTLY the same string using PHP like so:

$result_2 = db_query("SELECT userid FROM `tablename` WHERE name = 'addon' AND setting = 'day' AND value = '7' UNION SELECT userid FROM `tablename` WHERE name = 'addon' AND setting = 'month' AND value = '7' "); 
echo db_num_rows($result_1);

[acoolguy]

@TheServant
I have tried them both ways in both the phpmyadmin sql section and in the php file and both ways is the same result which is
phpmyadim sql section result 4
php file result 1
its a real mystery to me i have tried a load of different ways and they all produce the same results in phpmyadmin sql section the result is 4 (correct)
php file the result =1 (wrong)

[TheServant]

It has to be how you are calling or counting it in PHP. Can you post your PHP script including your row counting line please?

[acoolguy]

@TheServant

$table = db_prefix("names");
        if (get_module_setting("allowindex","birthdayscroll") == '1'){ //check if allowed to show on index page
        $day = date("j");
        $month = date("m");
        $sql = "SELECT userid FROM " .$table. " WHERE modulename = 'addon' AND setting = 'day' AND value = '$day'";
        $sql1 = "SELECT userid FROM " .$table. " WHERE modulename = 'addon' AND setting = 'month' AND value = '$month'";
        $sql3 = $sql." UNION ".$sql1;
        $result3 = db_query($sql3);
        $row3 = db_fetch_assoc($result3);
        $num = db_num_rows($row3);
        $id = $row['userid'];
        output("`nNum = %s`n`n",$num);
 
}
 

[TheServant]

OK, basically once you call db_fetch_assoc() you destroy the original structure. If you move db_num_rows() above db_fetch_assoc() and echo that, it should be correct. You really need to use a while loop as I put above if you have more than one row, which in most cases you will.

[acoolguy]

Ok after a little rewrite i have this

$day = date("j");
$month = date("n");
$sql = "SELECT userid FROM " .$table. " WHERE name =  'addon' AND setting = 'day' AND value = '$day' UNION SELECT userid FROM " .$table. " WHERE name =  'addon' AND setting = 'month' AND value = '$month'";
$result = db_query($sql);
$row = db_fetch_assoc($result);
foreach($row as $playerid){
$sql = "SELECT name FROM " .db_prefix("accounts"). " WHERE acctid = '$playerid'";
$result = db_query($sql);
$row = db_fetch_assoc($result);
$name = $row['name'];
output("UserId = %s`nName = %s`n",$playerid ,$name);
}
 

Yet it still picks only one when there are more :(

[TheServant]

The reason is on line 5. As soon as you call db_fetch_assoc() you lose the structure, unless it's in a while loop. Try:

$day = date("j");
$month = date("n");
$sql = "SELECT userid FROM " .$table. " WHERE name =  'addon' AND setting = 'day' AND value = '$day' UNION SELECT userid FROM " .$table. " WHERE name =  'addon' AND setting = 'month' AND value = '$month'";
$result = db_query($sql);
while($row = db_fetch_assoc($result)){
    echo $row['userid'];
}

[acoolguy]

@TheServant
Thanks for all the help so far however after trying the code like this ...

$day = date("j");
$month = date("n");
$sql = "SELECT userid FROM " .$table. " WHERE name =  'addon' AND setting = 'day' AND value = '$day' UNION SELECT userid FROM " .$table. " WHERE name =  'addon' AND setting = 'month' AND value = '$month'";
$result = db_query($sql);
while($row = db_fetch_assoc($result)){
output("UserId = %s`n",$row['userid']);
}
 

It now shows everyone regardless of the conditions in the sql statement ?

[TheServant]

I don't think you need UNION:

$sql = "SELECT userid FROM " .$table. " WHERE name = 'addon' AND ( (setting = 'day' AND value = '$day') OR (setting = 'month' AND value = '$month') );

I haven't done this for a while, but I think that should work.

[acoolguy]

@TheServant
Thanks a million i thought it might have been the database structure and that i would have to reorganize it in a better way however

$sql = "SELECT userid FROM " .$table. " WHERE name = 'addon' AND ( (setting = 'day' AND value = '$day') OR (setting = 'month' AND value = '$month') );
 

didn't work but

$sql = "SELECT userid FROM " .$table. " WHERE name = 'addon' AND setting = 'day' AND value = '$day' AND setting = 'month' AND value = '$month'";
 

did work once again thanks a million :)

[TheServant]

Haha, you know I was looking at your logic for a while and thought that that was the mistake, but then I just presumed you were doing something tricky. Glad it works.

[acoolguy]

Thanks again :)