mySQL and PHP please help

[jennypretty]

Hello,

I want to list more than 2 randome pictures from mySQL db and don't know how to fix this script to list 2 females and males from the db.

Can you please show me how to list more than 1 females or males from the db?
Thanks.
Jenny.

[guelphdad]

(SELECT id,
fname,
birthday,
pic1 FROM 
pro_membersu
where gender=1
order by rand()
limit 2
)
union
(SELECT id,
fname,
birthday,
pic1 FROM 
pro_membersu
where gender=2
order by rand()
limit 2
)

also a question or two for you?

are your birthdays in a valid date format? If not why not?

more importantly why do all the calculations on it in php when you can do them directly in mysql?

select month(birthday)

for instance would give you the month for the birthday

or if you are trying to calculate ages you can do that using date_sub and comparing against current_date().

much easier to get the data and calculations you need directly in the database if you ask me.

[chump2877]

PHP Code:

$q_males = "SELECT id,fname,birthday,pic1 FROM pro_membersu WHERE gender = 1"; 


change this to

PHP Code:

$q_males = "SELECT id,fname,birthday,pic1 FROM pro_membersu WHERE gender = 1 ORDER BY RAND() LIMIT 2"; 


And for

PHP Code:

$q_fems = "SELECT id,fname,birthday,pic1 FROM pro_membersu WHERE gender = 2"; 


change this to

PHP Code:

$q_fems = "SELECT id,fname,birthday,pic1 FROM pro_membersu WHERE gender = 2 ORDER BY RAND() LIMIT 2"; 


Then cycle through your result sets and adjust your HTML to display the results.

Edit: Use guelphdad's query instead of mine, it's more efficient

[jennypretty]

I changed as you two said, but it is not working.
$q_fems = "SELECT id,fname,birthday,pic1 FROM pro_membersu WHERE gender = 2 ORDER BY RAND() LIMIT 2";

Please look at this link www.ushomenow.com/vietnam , on the right, sometimes the pictures showed, soemtimes not, and errors.

Can you help please?

Thanks.

[guelphdad]

Check the query in mysql itself, make sure it is returning the information you think it is.

Also within your data, make sure you have pictures for those rows that are not displaying them. Maybe photos are missing in the database?

[jennypretty]

Well, the query does not return the data I wanted. Based on my understanding, Limit 2 means it returns 2 random pictures, but it returns only one pic.
thanks.

[chump2877]

My guess is there is something wrong with the code you are using to display the db results, and/or you are not cycling through all of the results....post your modified code, that will shed light on things for us.

And put it inside PHP tags...you don;t need to attach a script that small.

[jennypretty]

Here is my updated txt file.
thanks.

[bustamelon]

I don't know if this matters or not -- maybe someone can help me with this one -- but while you're looping thru the results of each query you're adding indexes to previously undefined arrays ($m_array, $f_array, etc). This seems like it's a scope problem, but I'm just guessing. I'm in the habit of declaring EVERYTHING on top, basically because of problems like this. Either do a print_r on those arrays after each query loop, or try just defining each of those arrays at the top of the script.

PHP Code:

$m_array = array();
$f_array = array();
etc 


If it still fails after that, echo the output values of the fields your pulling from the db and just make sure that you're getting what you expect there.

[chump2877]

This code is untested, and the code could be optimized more, but i think this is more along the lines of what you want to do:

PHP Code:

<center> 
<table border="0" width="100%"> 

<? 

$q_males = "SELECT id,fname,birthday,pic1 FROM pro_membersu WHERE gender = 1 ORDER BY RAND()";
$r_males = mysql_query($q_males); 

$x=0;

while($m = mysql_fetch_assoc($r_males))
{ 
    if($m["pic1"] != '') 
    { 
        $id = $m["id"];
        $fname = $m["fname"];
        $birthday = $m["birthday"];
        $pic1 = $m["pic1"];
        
        $m_day = substr($birthday,8,2); 
        $m_month = substr($birthday,5,2); 
        $m_year = substr($birthday,0,4); 
        if($m_month < date("m")) 
        {
            $age = date("Y")-$m_year; 
        }
        else 
        {
            $age = date("Y")-$m_year-1; 
        }
        
        
    ?><tr> 
        <td align="center"> 
        <a href="view.php?l=<? =$l ?>&id=<? echo $id; ?>"> 
        <img src="http://www.ushomenow.com/vietnam//members/uploads/<? echo $pic1; ?>" border="0" color="84B8D7" width="140"></a> 
        </td> 
    </tr>
    <tr> 
        <td align="center"><? echo $fname; ?> (<? echo $age; ?>)</td>
    </tr><?
    
        
        $x++
        
        if ($x == 2)
        {
            break;
        }
    } 
} 

$x=0;

$q_fems = "SELECT id,fname,birthday,pic1 FROM pro_membersu WHERE gender = 2 ORDER BY RAND()"; 
$r_fems = mysql_query($q_fems); 

while($f = mysql_fetch_assoc($r_fems))
{ 
    if($f["pic1"] != '') 
    { 
        $id = $f["id"];
        $fname = $f["fname"];
        $birthday = $f["birthday"];
        $pic1 = $f["pic1"];

        $f_day = substr($birthday,8,2); 
        $f_month = substr($birthday,5,2); 
        $f_year = substr($birthday,0,4); 
        if($f_month < date("m")) 
        {
            $age = date("Y")-$f_year; 
        }
        else 
        {
            $age = date("Y")-$f_year-1; 
        }    
        
        
    ?><tr> 
        <td align="center"> 
        <a href="view.php?l=<? =$l ?>&id=<? echo $id; ?>"> 
        <img src="http://www.ushomenow.com/vietnam//members/uploads/<? echo $pic1; ?>" border="0" color="84B8D7" width="140"></a> 
        </td> 
    </tr>
    <tr> 
        <td align="center"><? echo $fname; ?> (<? echo $age; ?>)</td>
    </tr><?    
    
        
        $x++
        
        if ($x == 2)
        {
            break;
        }        
    } 
} 

?> 

</table> 
</center>

[jennypretty]

It is not working.
My original code works fine but it lists ONLY 1 male and 1 female. I just want to list 2 random males and females.

Thanks.