PHP - Queries Not Working With Pdo

Hello all,

I've tried several ways to calculate a commission in the multi level marketing script, but it all ended up with nothing, recursive functions, nested sets nothing worked, and i'm running out of time to keep trying

I've made multiple queries on the same table, and it's working, but only the first 3 queries, not more, I tried to perform it 4 times (although I need the queries to be performed 13 times) but still only 3 times

example: A recruited B and C, and B recruited D, and D recruited E.... what's supposed to happen is A takes a commission on B, C, D, and E
but now it only takes commission on the first three, and no commission for anyone comes after that, and the same for B, C, D, and E each takes commission on only 3 members down the line, and no more

here is the queries

Code: [Select]
<?
 $result = mysql_query("SELECT * FROM users");


echo "<table width='589' border='1'>
<tr>
<th>ID</th>
<th>Name</th>
<th>National ID</th>
<th>Commission</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
 
$query = mysql_query("SELECT * from users where recruiteris = '".$row['id']."'");
$num_rows=mysql_num_rows($query);

              $id1 = $row['id']; 
               
                $commission = $num_rows;
               
               
            $_SESSION['id1'] = $id1;
               
  echo "<tr>";
  while ($roww = mysql_fetch_array($query))
  {
  $querry=mysql_query("select * from users where recruiteris = '".$roww['id']."'");
    $num_rowss = mysql_num_rows($querry);
  while ($rowws= mysql_fetch_array($querry))
   { $var3= '10';

  $querrys=mysql_query("select * from users where recruiteris = '".$rowws['id']."'");
    $num_rowsss = mysql_num_rows($querrys);
   while ($rowwss= mysql_fetch_array($querrys))
   
   {
    $querryss=mysql_query("select * from users where recruiteris = '".$rowwss['id']."'");
    $num_rowssss = mysql_num_rows($querryss);
   while ($rowwsss= mysql_fetch_array($querryss))
   {
   
   
    $querryr=mysql_query("select * from users where recruiteris = '".$rowwsss['id']."'");
    $num_rowssr = mysql_num_rows($querryr);
    }
    }
    }
    }
    $total= ($commission + $num_rowws + $num_rowwss + $num_rowwsss + $num_rowss + $num_rowssr) * $var3;
 echo  "<td><a href=\"user.php?id=".$row['id']."\">".$row['id']."</a></td>";
echo "<td>" . $row['fname'] .''. $row['lname'] ." </td>";
 echo"<td> ". $row['nid'] ." </td>";
  echo  "<td>".$total."</td>
   </tr>";
  }
 
echo "</table>";
        ?> 

I have two seperate piece of code.  One which sorts description and one which sorts price.  However, I am looking to almalgamate them into one.  I have tried the following however I appear to be way off. 

I want to select a product description with one link and then select a price range.  However, I want the price range to read the description. 

<?phpif( isset($_GET['description' ; 'price' ]))
$description = $_GET['description' ; 'price'];
$query = "SELECT * FROM productfeed WHERE description like '%$description%' LIMIT 0, 10";
$query = "SELECT * FROM productfeed WHERE price like '%$price%' LIMIT 0, 10";
$fetchdata = mysql_query($query) or die("query: $query<br>
This has an error: " . mysql_error() . '<br>');while($row = mysql_fetch_array($fetchdata)) {
$id = $row['id'];
$image = $row['awImage'];
$link = $row['link'];
$description = $row['description'];
$fulldescription = $row['fulldescription'];
$price = $row['price'];


<?phpif( isset($_GET['description']))
$description = $_GET['description'];
$query = "SELECT * FROM productfeed WHERE description like '%$description%' LIMIT 0, 10";
$fetchdata = mysql_query($query) or die("query: $query<br>
This has an error: " . mysql_error() . '<br>');while($row = mysql_fetch_array($fetchdata)) {
$id = $row['id'];
$image = $row['awImage'];
$link = $row['link'];
$description = $row['description'];
$fulldescription = $row['fulldescription'];
$price = $row['price'];


<?phpif( isset($_GET['price']))
$price = $_GET['price'];
$query = "SELECT * FROM productfeed WHERE price like '%$price%' LIMIT 0, 10";
$fetchdata = mysql_query($query) or die("query: $query<br>
This has an error: " . mysql_error() . '<br>');while($row = mysql_fetch_array($fetchdata)) {
$id = $row['id'];
$image = $row['awImage'];
$link = $row['link'];
$description = $row['description'];
$fulldescription = $row['fulldescription'];
$price = $row['price'];

Can any boy clarify these queries.
How do I find out if an array has values posted to each of its elements? I need to know that EVERY element has been filled out.
How can I send variables from a PHP script to another URL using POST without using forms and hidden variables?

I was wondering if it is possible to perform the following query.  Say I have a list of names each with an id number ranging from 1 to 10.  Is it possible to perform a query where you list all of these records while repeating one records.  For example could I do a query that lists record number 6 then 10, 9, 8, 7, 6 etc.  Or another example would be listing record number 10, then 10, 9, 8, 7, 6 etc?

Thanks for any help. 

Hello

I have this query and it is working well:
Code: [Select]
$data = mysql_query("
SELECT `title`, `body`, 'condoms_en' as REF FROM `condoms_en`
UNION
SELECT `title`, `body`, 'discr_en' as REF FROM `discr_en`
UNION
SELECT `title`, `body`, 'diseases_en' as REF FROM `diseases_en`
UNION
SELECT `title`, `body`, 'express_en' as REF FROM `express_en`
UNION
SELECT `title`, `body`, 'gender_en' as REF FROM `gender_en`
UNION
SELECT `title`, `body`, 'get_informed_en' as REF FROM `get_informed_en`
UNION
SELECT `title`, `body`, 'get_tested_en' as REF FROM `get_tested_en`
UNION
SELECT `title`, `body`, 'newly_en' as REF FROM `newly_en`
UNION
SELECT `title`, `body`, 'news_hiv_en' as REF FROM `news_hiv_en`
UNION
SELECT `title`, `body`, 'nutrition_en' as REF FROM `nutrition_en`
UNION
SELECT `title`, `body`, 'phdp_en' as REF FROM `phdp_en`
UNION
SELECT `title`, `body`, 'press' as REF FROM `press`
UNION
SELECT `title`, `body`, 'r_o_en' as REF FROM `r_o_en`
UNION
SELECT `title`, `body`, 'stats_en' as REF FROM `stats_en`
UNION
SELECT `title`, `body`, 'stigma_en' as REF FROM `stigma_en`
UNION
SELECT `title`, `body`, 'think_news_en' as REF FROM `think_news_en`
UNION
SELECT `title`, `body`, 'think_reports' as REF FROM `think_reports`
UNION
SELECT `title`, `body`, 'treatment_en' as REF FROM `treatment_en`
where`title` like '%$kw%' OR `body` like '%$kw%'") or die(mysql_error());
$rows = mysql_num_rows($data);

I need to add to the same query this part:
Code: [Select]
SELECT `cont`, 'about_us_en' as REF FROM `about_us_en`
UNION
SELECT `que`,`ans`, 'faq_en' as REF FROM `faq_en`
UNION
SELECT `orga`, `lnk`, 'links_en' as REF FROM `links_en`
UNION
SELECT `title`, `desc`, 'publications' as REF FROM `publications`

IF I add this part as is, I get an error saying that the select has different columns.

Please Help and how do I modify the where if they are combined?!

Thank you

I need two queries cause I don't know how to put them into one. Problem here is I need to know the field Liters from the database in the first query to subtract it from the second query to be able to calculate the average fuel consumption of the chosen range.   This is my code, hope you can help

function getContent() {
	include 'connection.php';

$dx = date('m', strtotime('today - 30 days'));	

	if (!isset($_POST["month_select"])) {
		$month = $dx;
		$year = "2014";
	}
	else {
		$month = $_POST["month_select"];
		$year = $_POST["year_select"];
	}

//last fuel fill value 
		$query2 = "SELECT d.liter as FirstFill 
  FROM members AS m
LEFT OUTER
  JOIN diesel AS d
    ON d.userid = m.id
   WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 ORDER BY d.id ASC LIMIT 1";

		$sql2=$oDB->prepare($query2);
		$sql2->execute();
		$row2 = $sql2->fetchAll();
    return $row2;
	
    $FirstFill = $row2['FirstFill'];
	
		$query = "SELECT m.username, m.id, 10 * (SUM(d.liter) - $FirstFill) / (MAX(d.km) - MIN(d.km)) as AvgFuel 
  FROM members AS m
LEFT OUTER
  JOIN diesel AS d
    ON d.userid = m.id
   WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0
GROUP
    BY m.id ORDER BY AvgFuel ASC";
	
		$sql=$oDB->prepare($query);
		$sql->execute();
		$row = $sql->fetchAll();
    return $row;

	}

Edited by Aero77, 23 May 2014 - 12:39 PM.

Hi, I am trying two compare to dates but have not been successful.

A row is filled in Mysql database called htime with   $check=mktime(17,0,0,04,10,2011);

My first question is  that in the mktime function I have entered 17 which is hours. I would like to know if thats like the 17th hour of the given day or thats not how it works?

My second question is it a valid query to use <= or >= or < in a  mysql query for example.

$query="SELECT * FROM hdb WHERE htime <='".time()."'";

Thirdly I want to know if a row is filled with mktime(17,0,0,04,10,2011);  for example How can I only extract the day from the database based on the time has passed? This is because my own query doesnt seem to be working which is the query above I have no errors but the results dont seem to be right. Any help is much appreciated thanks.

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=331128.0

Hello, in short, here is my problem:

There are 2 tables:

a table called postcodes, which contains UK postcodes
and a table called wp_bp_xprofile_data (i want to integrate this search with the Buddypress plugin's tables) which contains the user data.

I tried searching this, but to be honest, i didn't quite know how to go about it..



Here is what i have:

$query1 = "SELECT value, user_id, (SQRT(POW((b.x - a.x), 2) + POW((b.y - a.y), 2))/1000) * 0.621 AS distance
    FROM postcodes a, postcodes b, wp_bp_xprofile_data
    WHERE a.outcode = '"$postcode"' AND b.outcode = wp_bp_xprofile_data.value
    HAVING (distance < '"$area"')
    ORDER BY distance asc ";


$result1=mysql_query($query1) or die(mysql_error());

        echo "<p style=\"font-size:10px;line-height:14px;color:#888;\">Straight line distances shown.<br>";
        echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" ><tr>";

        // display results
        while ($list = mysql_fetch_array($result1)) {
        $user_postcode = $list['value'];
        $distance = $list['distance'];
        $distance = round($distance);

   echo "<td align=\"right\">$distance miles away</td></tr>";
   echo "<tr><td colspan=\"3\" align=\"left\" width=\"130px\">$user_postcode </td>";
   echo "<td align=\"right\"><a href=\"blahblah\">Directions using Google Maps</a></td>";
   echo "<tr><td align=\"left\">&nbsp;</td><tr>";
        echo "</tr>";
}
echo "</table>";


Code: ("wp_bp_xprofile_data") [Select]
id field_id user_id value
1 1          1 admin
8 3          3 NW10
6 1          3 Test User 1
9 4          3 IT, Web Development
11 1          4 Test User 2
12 3          4 HA5
13 4          4 Test, It, Some Work
Code: ("postcodes") [Select]
outcode x y latitude longitude
B10 392900 804900 57 -2
AB11 394500 805300 57 -2
AB12 393300 801100 57 -2
AB13 385600 801900 57 -2
AB14 383600 801100 57 -2


Here is an example of what i need:

field_id "3" corresponds to a postcode. My script so far, can search a postcode and return results with the postcodes sorted by distance. What i'm trying to do, is also get the username on the result (i.e the 'value' column again..).

in short, im trying to search a postcode, find it on wp_bp_xprofile_data, associate it with the correspondent user, and return both.  Someone told me i need to INNER JOIN, but im lost..



The end result im looking for is if i type the search: "NW9 1AA"

It returns:

Test User 1
NW10
 

Test User 2
HA5   
(the code sorts them by distance. So far i can get it to return the postcode, but cannot associate the relevant name.

Can anyone help me with this?
I hope this is in the right forum.

Thanks for your attention.

The whole code is below, followed by the part that isn't producing.  It all works extremely well until the last part, and I have two instances of setting a variable to mysql_fetch_assoc that work.  Not sure why the third attempt doesn't.  I'm just trying to have a separate area that outputs 'msg'.

Code: [Select]

$sql = "SELECT * FROM players as p
INNER JOIN schools as s
WHERE p.tid = s.id
ORDER BY status, playerLast";

$results = mysql_query($sql);



echo '<div class="roster">';

$team = mysql_fetch_assoc($results);

echo '<div class="team_info">
<div class="school">' . $team['school'] . '</div>
<div class="coach">Coach ' .$team['coachFirst'] . ' ' . $team['coachLast'] .'</div>
<div>Sectional: ' . $team['sectional'] . '</div>
<div>Class: ' . $team['class'] . 'A</div>
';

echo '</div>';


  $currentStatus = false;  //Flag to detect change in status
    while($player = mysql_fetch_assoc($results))
    {


        if($currentStatus != $player['status'])
        {
            //Status has changed, display status header
            $currentStatus = $player['status'];
echo '<br><b>';
if ($currentStatus == '1')  {echo 'Returning Starters';}
elseif ($currentStatus == '2')  {echo 'Key Returners';}
elseif ($currentStatus == '3')  {echo 'Varsity Newcomers';}
elseif ($currentStatus == '4')  {echo 'Key Freshmen';}
echo '</b><br>';
           // echo "<div><b>{$currentStatus}</b></div>\n";
        }
        //Display player info
        echo $player['playerFirst'] . ' ' . $player['playerLast'] . ', ' . $player['feet'] . '\'' . $player['inches'] . '",' . $player['position'] . ', ' . $player['year'] . ';<br>';
 
    }

        //Player comments
echo '<hr>';
echo '<div class="coach_comments"><span class="player_name">hello';

while($comments = mysql_fetch_assoc($results)) {


echo $comments['playerFisrt'] . ' ' . $comments['playerLast'] . ':  </span>' . $comments['msg'];
}
echo '</div>';
echo '</div>';



It's all working until what's below.  All I get out of it is the "hello", which is just a test to make sure it wasn't a CSS issue.

Quote


        //Player comments
   echo '<hr>';
   echo '<div class="coach_comments"><span class="player_name">hello';
   
   while($comments = mysql_fetch_assoc($results)) {

      
      echo $comments['playerFisrt'] . ' ' . $comments['playerLast'] . ':  </span>' . $comments['msg'];
   }
echo '</div>';
echo '</div>';

Hi everyone

I have the following PHP code

$sql.= "INSERT INTO `data` (`info`, `write`, `date`) VALUES";
$sql.= "(";
        for ($c=0; $c < $num; $c++) {
$sql.= '"'.str_replace('"', "", $data[$c]).'",';
        }
$sql.= "'".date('Y-m-d')."');";

If I

print $sql;

I get

Code: [Select]
INSERT INTO `data` (`info`, `write`, `date`) VALUES("data99","n",'2010-10-05');
INSERT INTO `data` (`info`, `write`, `date`) VALUES("data101","y",'2010-10-05');
INSERT INTO `data` (`info`, `write`, `date`) VALUES("data876","n",'2010-10-05');
what would I need to do to my PHP code in order for it to process each QUERY either all at once or one at a time

Whatever I try, it either inserts nothing, or just does the first INSERT

Any ideas?

Thanks

I'm trying to figure out how to combine these queries, to decrease server load and load time, but I'm not so good with MySQL queries. Does anyone have any ideas, any help is greatly appreciated, I've been tinkering with this for days now.

$Actv = 0;
$ActvCount = -1;
$ActvUpgrade = 0;
$ActvRenewal = 0;
$ActvVehicleAdds = 0;

$result2 = mysql_query("SELECT * FROM tblOperators WHERE OperatorLocale = 'USA' and OperatorStatus = 'ACTIVE' and Team = 'RENEWALS'");
while($row2 = mysql_fetch_array($result2)) {
 $operID = $row2['OperatorID'];
 
 $result = mysql_query("SELECT * FROM tblUserPayments WHERE OperatorID = '$operID' AND PaymentStatus='OK' AND PaymentDate LIKE '$currentDate%'");

 while($row = mysql_fetch_array($result))
 {
	if($row['PaymentReason'] == 'ACTIVATION'){
		++$ActvCount;
		//if($row['PaymentMethod'] == 'CREDITCARD'){
			$ActvUpgrade += $row['ChargeAmount'];
		//}
	}

	elseif($row['PaymentReason'] == 'UPGRADE'){
		$userid = $row['UserID'];
		$paymentdate = $row['PaymentDate'];
		$result1 = mysql_query("SELECT * FROM tblRenewalInvoices WHERE UserID='$userid' AND ('$paymentdate' >= DATE_SUB(DueDate, INTERVAL 90 DAY) AND '$paymentdate' < DATE_ADD(DueDate, INTERVAL 15 DAY)) AND ParentInvoiceID IS NULL ORDER BY InvoiceNum DESC LIMIT 1");
		if( $row1 = mysql_fetch_array($result1)) {
			$packageid = $row['PackageID'];
			$pack = mysql_query("SELECT * FROM tblUserPackages WHERE PackageID='$packageid';");
			if($pack1 = mysql_fetch_array($pack)){

				$expDate = $pack1['ExpirationDate'];
				$dueDate = $row1['DueDate'];
				$days = mysql_fetch_row(mysql_query("SELECT TO_DAYS('$expDate')-TO_DAYS('$dueDate');"));
				$months = (int)( ((int)$days + 14) / 30.4);
				$years = (int) ( ((int)$days + 182) / 365);
				$Intervals = 0;
				if($years > 0){
					$Intervals = $years;
				}
				if(($pack1['Package'] or 'GPS-SVL') or ($pack1['Package'] == 'GPS-1') or ($pack1['Package'] == 'GPS-1PLUS')){
					if($Intervals > 1){
						//if($row['PaymentMethod'] == 'CREDITCARD'){
							$Actv += $row['ChargeAmount'];
						//}
					}
					else{
						//if($row['PaymentMethod'] == 'CREDITCARD'){
							$ActvRenewal += $row['ChargeAmount'];
						//}
					}
				}
				else{
					$Actv += $row['ChargeAmount'];
				}
			}
			else{
			}
		}
		else{
			//if($row['PaymentMethod'] == 'CREDITCARD')
				$ActvUpgrade += $row['ChargeAmount'];
		}
	}

	elseif($row['PaymentReason'] == 'ADDVEHICLE'){
		//if($row['PaymentMethod'] == 'CREDITCARD')
			$ActvVehicleAdds += $row['ChargeAmount'];
	}
		
 }

$result = mysql_query("SELECT * FROM tblRenewalCalls WHERE OperatorID = '$operID' AND PayStatus='OK' AND DateSubmitted LIKE '$currentDate%'");

 while( $row = mysql_fetch_array($result) )
 {
	if($row['Charged']){
		if ((int)$row['RenewYears'] > 1) {
			$Actv += $row['RenewTotal'];
		}
		else{
			$ActvRenewal += $row['RenewTotal'];
		}
	 }
  }
 }
$total = $Actv+$ActvRenewal+$ActvUpgrade+$ActvVehicleAdds;

$ActvRenewal = $total - ($ActvVehicleAdds + $ActvUpgrade);

$upgradeEarned = $ActvUpgrade;
$renewalEarned = $ActvRenewal;

This must be easy enough, but I'm a noob so go easy on me, please :-)   I have two pieces of code and they're working well on their own. All I need is to put these two conditions into one query.   The first one:

<?php
function childtheme_cat_limited_blog( $query ) {
    if ( $query->is_home() && $query->is_main_query() ) {
        $query->set( 'events_categories', 'mycategory1' );
    }
}
add_action( 'pre_get_posts', 'childtheme_cat_limited_blog' );
?>

And the second:

<?php
function childtheme_cat_limited_blog( $query ) {
    if ( $query->is_home() && $query->is_main_query() ) {
        $query->set( 'category_name', 'mycategory2' );
    }
}
add_action( 'pre_get_posts', 'childtheme_cat_limited_blog' );
?>

I'm sure it's a piece a cake for you guys, but I can't figure it out for the life of me, so any help would be greatly appreciated!   Thank you!   (My goal is here to display posts from two categories on a homepage of a Wordpress site, but to complicate things the two categories are from two different post types.)

Ive just implemented a function which shows how many queries have been run on the specific page. Since the result returned 38 queries on the index page alone i need a way of listing all of these queries. I could just go through the code and find them all but there are several pages to go through. so does anyone know of a function which will list all the queries that have been run?