mysql/php queries help needed.

[acctman]

I hope this is not to much to ask for in a post, but any help would be apperciated. my site script uses a template .tpl system similar to smarty... to for each $en[] query i'll be able to post the results below in the html by using <% %>

 

I attempted to start writing the queries but i think i need to use something like mysql_result(mysql_query("SELECT... for each.

 

<?
$ref = mysql_query("SELECT `ref_id`,`ref_userid`,`ref_mem`,`ref_points`,`ref_status` FROM `rate_referrals` WHERE `ref_userid` = '".$en['m_id']."'");
$en = mysql_fetch_array($ref);

$en['totalref'] = ''; look in 'rate_referrals' table Where ref_userid = .$en['m_id']. total amount of entries for user

$en['totalm'] = ''; // look in 'rate_referrals' table Where ref_userid = .$en['m_id']. collect all rows them go to 'rate_members' table and check 'm_type' for each 'ref_mem'. If m_type is 1 then MALE. display total # amount of males

$en['totalf'] = ''; // look in 'rate_referrals' table Where ref_userid = .$en['m_id']. collect all rows them go to 'rate_members' table and check 'm_type' for each 'ref_mem'. If m_type is 2 then FEMALE. display total # amount of Females

$en['totalpts'] = ''; // rate_referrals table WHERE ref_userid = .$en['m_id']. tally all points in the ref_points field

$en['totalpend'] = ''; // rate_referrals table WHERE ref_userid = .$en['m_id']. tally total pending in the ref_status field If ref_status is 0 or 1

$en['pendadmin'] = ''; // rate_referrals table WHERE ref_userid = .$en['m_id']. tally total pending in the ref_status field If ref_status is 0

$en['pendactive'] = ''; // rate_referrals table WHERE ref_userid = .$en['m_id']. tally total pending in the ref_status field If ref_status is 1

$en['ref_mem'] = ''; // rate_referrals table look at field ref_mem WHERE ref_userid = .$en['m_id']. for each row display below in html

$en['refm_sex'] = ''; // rate_referrals table look at field ref_mem WHERE ref_userid = .$en['m_id']. then go to rate_members table and lookup WHERE ref_mem = m_id and the get m_type ... If m_type = 1 then Male ... for each row display below in html

$en['refm_date'] = ''; // rate_referrals table look at field ref_mem WHERE ref_userid = .$en['m_id']. then go to rate_members table and lookup WHERE ref_mem = m_id and the get m_date ... 12-31-2007 format... for each row display below in html

$en['refm_status'] = ''; // rate_referrals table look at field ref_mem WHERE ref_userid = .$en['m_id']. If result 
0: Pending Account 1: Pending Activity 2: Approved, display the proper text in HTML for each result.
?>

 

 

<b>Referral info...</b><br>

Total Referred:<%totalref%><br>

Total Females:<%totalf%><br>

Total Males:<%totalm%><br>

Total Referral Points:<%totalpts%><br>

<br><br>

Total Pending Referrals:<%totalpend%><br>

Pending Admin Approval:<%pendadmin%><br>

Pending Activity Approval:<%pendactive%><br>

<br><br>

<b>MemberName - Gender - Date Joined - Status</b>

<br>

<%ref_mem%> -  <%refm_sex%> - <%refm_date%> - <%refm_status%>

display 10 then Paginating the rest

 

[BlueSkyIS]

I hope this is not to much to ask for in a post...

 

Please post your question in the form of a question.

 

[acctman]

I hope this is not to much to ask for in a post...

 

Please post your question in the form of a question.

 

 

I need help with the mysql queries for each function... focusing more on the queries that require accessing two tables like $en['totalm'] for example.

[acctman]

hmm, guess it's to much... would anyone be willing to help me for $40 via paypal?

[grimmier]

<?php
$userid = $_POST['userid'];
$ref = mysql_query("SELECT `ref_id`,`ref_userid`,`ref_mem`,`ref_points`,`ref_status` FROM `rate_referrals` WHERE `ref_userid` = '{$userid}'");
$en = mysql_fetch_array($ref);

$en['totalref'] = "SELECT COUNT(*) AS `Count` FROM `rate_referrals` WHERE `ref_userid` = '{$userid}' Group by `ref_userid` Order By `Count` "; 
//look in 'rate_referrals' table Where ref_userid = .$en['m_id']. total amount of entries for user

// not to sure about these next 2. would help to see the structure of the tables.
$en['totalm'] = "SELECT COUNT(*) `Count` FROM ( SELECT * FROM `rate_members`, `rate_referrals` WHERE `rate_referrals`.`ref_userid` = '{$userid}' and `rate_referrals`.`ref_userid` = `rate_members`.`ref_userid` ) WHERE `m_type` = '1' as temp  Group BY `m_type` Order BY `m_type` "; 
// look in 'rate_referrals' table Where ref_userid = .$en['m_id']. collect all rows them go to 'rate_members' table and check 'm_type' for each 'ref_mem'. If m_type is 1 then MALE. display total # amount of males

$en['totalf'] = "SELECT COUNT(*) `Count` FROM ( SELECT * FROM `rate_members`, `rate_referrals` WHERE `rate_referrals`.`ref_userid` = '{$userid}' and `rate_referrals`.`ref_userid` = `rate_members`.`ref_userid` ) WHERE `m_type` = '2' as temp  Group BY `m_type` Order BY `m_type` "; 
// look in 'rate_referrals' table Where ref_userid = .$en['m_id']. collect all rows them go to 'rate_members' table and check 'm_type' for each 'ref_mem'. If m_type is 2 then FEMALE. display total # amount of Females

$en['totalpts'] = "SELECT sum(ref_points) as `total` FROM `rate_referrals` WHERE  `ref_userid` = '{$userid}' ";
// rate_referrals table WHERE ref_userid = .$en['m_id']. tally all points in the ref_points field

$en['totalpend'] = "SELECT COUNT(*) `Count` FROM `rate_referrals` WHERE `ref_userid` = '{$userid}' and ( `ref_status` = '0' or `ref_status` = '1' ) GROUP BY `ref_userid` "; 
// rate_referrals table WHERE ref_userid = .$en['m_id']. tally total pending in the ref_status field If ref_status is 0 or 1

$en['pendadmin'] = "SELECT COUNT(*) `Count` FROM `rate_referrals` WHERE `ref_userid` = '{$userid}' and `ref_status` = '0' GROUP BY `ref_userid` "; 
// rate_referrals table WHERE ref_userid = .$en['m_id']. tally total pending in the ref_status field If ref_status is 0

$en['pendactive'] = "SELECT COUNT(*) `Count` FROM `rate_referrals` WHERE `ref_userid` = '{$userid}' and `ref_status` = '1' GROUP BY `ref_userid` "; 
// rate_referrals table WHERE ref_userid = .$en['m_id']. tally total pending in the ref_status field If ref_status is 1

$en['ref_mem'] = "SELECT * FROM `rate_referrals` WHERE `ref_userid` = '{$userid}' "; 
// rate_referrals table look at field ref_mem WHERE ref_userid = .$en['m_id']. for each row display below in html

$en['refm_sex'] = "SELECT * FROM `rate_members` WHERE `rate_referrals`.`ref_userid` = '{$userid}`  AND `rate_referrals`.`ref_mem` = `rate_members`.`m_id` AND `rate_members`.`m_type` = 1 "; 
// rate_referrals table look at field ref_mem WHERE ref_userid = .$en['m_id']. then go to rate_members table and lookup WHERE ref_mem = m_id and the get m_type ... If m_type = 1 then Male ... for each row display below in html

$en['refm_date'] = "SELECT DATE_FORMAT(`m_date`, '%m-%d-%Y') FROM `rate_members` WHERE `rate_referrals`.`ref_userid` = '{$userid}`  AND `rate_referrals`.`ref_mem` = `rate_members`.`m_id` "; 
// rate_referrals table look at field ref_mem WHERE ref_userid = .$en['m_id']. then go to rate_members table and lookup WHERE ref_mem = m_id and the get m_date ... 12-31-2007 format... for each row display below in html

$en['refm_status'] = "SELECT `ref_mem` FROM `rate_referrals` WHERE `ref_userid` = '{$userid}' "; 
// rate_referrals table look at field ref_mem WHERE ref_userid = .$en['m_id']. If result 0: Pending Account 1: Pending Activity 2: Approved, display the proper text in HTML for each result.

?>

 

those should work for you. I replaced the userid var, figuring that it was assigned from a form entry.  you had it being set from the result of the query where you call it. it won't work that way.

 

if i had the structure of the tables with some sample data to play with it would be much easier.

[acctman]

here's how the table structure. every statement needs to have something like mysql_result(mysql_query("SELECT... right? or mysql_result_row or something since the data has to be pull using mysql query.

 

table: rate_referrals:

ref_id, ref_userid, ref_mem, ref_points, ref_status

 

table: rate_members:

m_id, m_user, m_type, m_date, m_email

 

I think the best way to do this and not confuse myself or others is to just echo the the html with result and not use the smarty type template setup that my site has.

 

new setup, does this make more sense? (see grimmier) post for

 

$ref = mysql_query("SELECT `ref_id`,`ref_userid`,`ref_mem`,`ref_points`,`ref_status` FROM `rate_referrals` WHERE `ref_userid` = '{$userid}'");

$en['totalref'] = "SELECT COUNT(*) AS `Count` FROM `rate_referrals` WHERE `ref_userid` = '{$userid}' Group by `ref_userid` Order By `Count` "; 

// not to sure about these next 2. would help to see the structure of the tables.
$en['totalm'] = "SELECT COUNT(*) `Count` FROM ( SELECT * FROM `rate_members`, `rate_referrals` WHERE `rate_referrals`.`ref_userid` = '{$userid}' and `rate_referrals`.`ref_userid` = `rate_members`.`ref_userid` ) WHERE `m_type` = '1' as temp  Group BY `m_type` Order BY `m_type` "; 
// look in 'rate_referrals' table Where ref_userid = .$en['m_id']. collect all rows them go to 'rate_members' table and check 'm_type' for each 'ref_mem'. If m_type is 1 then MALE. display total # amount of males

$en['totalf'] = "SELECT COUNT(*) `Count` FROM ( SELECT * FROM `rate_members`, `rate_referrals` WHERE `rate_referrals`.`ref_userid` = '{$userid}' and `rate_referrals`.`ref_userid` = `rate_members`.`ref_userid` ) WHERE `m_type` = '2' as temp  Group BY `m_type` Order BY `m_type` "; 
// look in 'rate_referrals' table Where ref_userid = .$en['m_id']. collect all rows them go to 'rate_members' table and check 'm_type' for each 'ref_mem'. If m_type is 2 then FEMALE. display total # amount of Females

$en['totalpts'] = "SELECT sum(ref_points) as `total` FROM `rate_referrals` WHERE  `ref_userid` = '{$userid}' ";

$en['totalpend'] = "SELECT COUNT(*) `Count` FROM `rate_referrals` WHERE `ref_userid` = '{$userid}' and ( `ref_status` = '0' or `ref_status` = '1' ) GROUP BY `ref_userid` "; 

$en['pendadmin'] = "SELECT COUNT(*) `Count` FROM `rate_referrals` WHERE `ref_userid` = '{$userid}' and `ref_status` = '0' GROUP BY `ref_userid` "; 

$en['pendactive'] = "SELECT COUNT(*) `Count` FROM `rate_referrals` WHERE `ref_userid` = '{$userid}' and `ref_status` = '1' GROUP BY `ref_userid` "; 

$en['ref_mem'] = "SELECT * FROM `rate_referrals` WHERE `ref_userid` = '{$userid}' "; 

$en['refm_sex'] = "SELECT * FROM `rate_members` WHERE `rate_referrals`.`ref_userid` = '{$userid}`  AND `rate_referrals`.`ref_mem` = `rate_members`.`m_id` AND `rate_members`.`m_type` = 1 "; 

$en['refm_date'] = "SELECT DATE_FORMAT(`m_date`, '%m-%d-%Y') FROM `rate_members` WHERE `rate_referrals`.`ref_userid` = '{$userid}`  AND `rate_referrals`.`ref_mem` = `rate_members`.`m_id` "; 

$en['refm_status'] = "SELECT `ref_mem` FROM `rate_referrals` WHERE `ref_userid` = '{$userid}' ";

echo "Total Referred: .$en['totalref']. \n";
echo "Total Females: .$en['totalm']. \n";
echo "Total Males: .$en['totalm']. \n";
echo "Total Referral Points: .$en['totalpts']. \n";

echo "Total Pending Referrals: .$en['totalpend']. \n";
echo "Pending Admin Approval: .$en['pendadmin']. \n";
echo "Pending Activity Approval: .$en['pendactive']. \n";


echo "MemberName - Gender - Date Joined - Status\n";
while($en = mysql_fetch_array($ref)) {

echo ".$en['ref_mem']. -  .$en['refm_sex']. - .$en['refm_date']. - .$en['refm_status'].\n";

}

[grimmier]

table: rate_referrals:

ref_id, ref_userid, ref_mem, ref_points, ref_status

 

table: rate_members:

m_id, m_user, m_type, m_date, m_email

 

is the ref_id supposed to be the same as m_id?

[acctman]

table: rate_referrals:

ref_id, ref_userid, ref_mem, ref_points, ref_status

 

table: rate_members:

m_id, m_user, m_type, m_date, m_email

 

is the ref_id supposed to be the same as m_id?

 

ref_userid = m_id

ref_id is just for keeping count. in the rate_members table the m_id is used as the member ID

 

since i've decided to echo the html output to make things easier... then do a while loop to print out all the referred (ref_mem) for profile user.

 

$en['m_id'] = rate_members userid m_id this is global so you can reference ref_userid = $en['m_id'].

the logged in member viewing there profile page = $en['m_id']

[acctman]

does this look right?

$en['totalref'] = mysql_num_rows(mysql_query("SELECT `ref_id` FROM `rate_referrals` WHERE `ref_userid` = '{$userid}'"));

 

can I use LEFT JOIN to optimize this coding?

$en['totalm'] = "SELECT COUNT(*) `Count` FROM ( SELECT * FROM `rate_members`, `rate_referrals` WHERE `rate_referrals`.`ref_userid` = '{$userid}' and `rate_referrals`.`ref_userid` = `rate_members`.`ref_userid` ) WHERE `m_type` = '1' as temp  Group BY `m_type` Order BY `m_type` ";