PHP - Joined Queries?

Hi there,
is it possible to join a variable and a string inside a $_POST variable inside a mysql query (UPDATE in this case)

Here is what i am trying to accomplish:

$update = "UPDATE mona SET STKFF='$_POST[$counter."NAME"]' WHERE id='$userid'";

if (!mysql_query($update))
  {
  die('Error: ' . mysql_error());
  }
else echo " <br> Update Complete";

Its the '$_POST[$counter."NAME"]' bit that im worried about, is this possible without having to do:
$foo=$counter."NAME"
'$_POST[$foo]'


Thanks
Chris

Hi,    I have the following query 

SELECT 
user_details.User_club_ID, 
user_details.fname, 
user_details.lname, 
user_details.email, 
user_details.club_No 
club.CLUBCODE, 
club.club_id
FROM user_details, club
WHERE club_id = $cid AND user_details.club_No = club.CLUBCODE AND user_status = 'active'";

which I converted to a prepared statement as 

SELECT 
user_details.User_club_ID, 
user_details.fname, 
user_details.lname, 
user_details.email, 
user_details.club_No 
club.CLUBCODE, 
club.club_id
FROM user_details, club
WHERE club_id = ? AND user_details.club_No = club.CLUBCODE AND user_status = ?";

Please note that user_status is a field in the table user_details. The original query (non -PDO) works correctly.   I want to know if this is correct and that the comparison in the WHERE clause  i.e. user_details.club_No = club.CLUBCODE is security safe. If not then how should this be modified.   Also if there is a better way to write this statement, kindly show that as well.  Thanks    Thanks all !
Edited by ajoo, 11 December 2014 - 02:35 AM.

Back with a new problem. 

I have 8 tables interconnected. 

Table#1 - Users
user_id | name

Table#2 - user_categories
id | user_id | category_id

Table#3 - user_cities
id | user_id | city_id

Table#4 - user_dates
id | user_id | dates_available

Table#5 - categories
category_id | category_name

Table#6 - cities
city_id | city_name

Table#7 - provinces
province_id | province_name

Table#8 - categories
country_id | country_name

Each user will have multiple categories, cities and available dates listed in these tables. I simply want to retrieve and list each user and their data on a page. 

Here's my query.

$url_city = 1;
$url_category = 2;
$url_date = '2021-07-19';

$find_records = $db->prepare("SELECT user_categories.*, categories.*, user_cities.*, cities.*, provinces.*, countries.*, user_dates.*, users.* FROM users
LEFT JOIN user_categories ON users.user_id = user_categories.user_id
LEFT JOIN user_cities ON users.user_id = user_cities.user_id
LEFT JOIN user_dates ON users.user_id = user_dates.user_id
LEFT JOIN categories ON user_categories.category_id = user_categories.category_id
LEFT JOIN cities ON user_cities.city_id = cities.city_id
LEFT JOIN provinces ON cities.province_id = provinces.province_id
LEFT JOIN countries ON provinces.country_id = countries.country_id
WHERE user_cities.city_id = :city_id AND user_categories.category_id = :category_id AND user_dates.date_available = :date_available GROUP BY users.user_id");
$find_records->bindParam(':city_id', $url_city);
$find_records->bindParam(':category_id', $url_category);
$find_records->bindParam(':date_available', $url_date);
$find_records->execute();
$result_records = $find_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_records) > 0) {
	foreach($result_records as $row) {

		$user_id    	  =	$row['user_id'];
        $name    		  =	$row['name'];

        $country_id       =	$row['country_id'];
        $country_code     =	$row['country_code'];
        $country_name     =	$row['country_name'];

        $province_id      =	$row['province_id'];
        $province_code    =	$row['province_code'];
        $province_name    =	$row['province_name'];

        $city_id          =	$row['city_id'];
        $city_name        =	$row['city_name'];

        $category_id      =	$row['category_id'];
        $category_name    =	$row['category_name'];

     }
}

There are no errors but the above query would only return a single row with only 1 "user" despite having multiple users in the "users" table. If I remove the GROUP BY, then it'll return multiple rows of the same user instead of all the relevant users.

So what do you think I am doing wrong with my query? 

Edited July 20 by imgrooot

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>";
        ?> 

noob question: I have following two queries I'd like to combined into one - how is this done?

$temp = @mysql_query("SELECT * FROM purchased_leads WHERE leadID = '{$_REQUEST[leadid]}'");

"SELECT refundNotes FROM leads WHERE leadID = '{$_REQUEST[leadid]}'"

ive had these queries working okay until i checked on it today, it's not working anymore..
these are the errors:::
Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\arrastre\add.php on line 105
Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\arrastre\add.php on line 117


if(isset($_POST['add'])){
if($billnmbr=="" or $orno=="" or $payor=="" or $arrastre=="" or $wharfage=="" or $total=="" or $date=="" or $tcl==""){
echo "At least one field was left blank.";
}
else{
$query = mysql_query("select * from `arrastre` WHERE `billnmbr`='$billnmbr'"); /*****************this is my line 105************/
$count = mysql_num_rows($query);
if($count==1){
echo "This billnumber is already in the database.";
}

else{
$bll = strtoupper($billnmbr);
$payr = strtoupper($payor);
$query="insert into `arrastre` (`billnmbr`, `orno`, `payor`, `arrastre`, `wharfage`, `total`, `date`, `tcl`)
values ('$bll', '$orno', '$payr', '$arrastre', '$wharfage', '$total', '$date', '$tcl')";
$result=mysql_query($query);

$query = mysql_query("select * from `arrastre` where `billnmbr`= '$billnmbr'", $link); /************and this is my line 117*************/
$count = mysql_num_rows($query);
if($count==1){
echo "last bill number added: ".$billnmbr;
}
}
Thank you very much for your time and have a nice day.

Good day everyone. I go by the nickname Sbosh and I am a newbie in PHP.

I am currently learning php using video tutorials. I need help with regards to php queries.

I have the following code:

"SELECT 'food', 'calories' FROM 'diet' ORDER BY 'id'"

which is supposed to display content from a table inside phpmyadmin which i created manually using phpmyadmin. But It gives an error saying i must check the MariaDB version on how to right this query, something like that.

Please assist on the proper way of writing this code. Again I am a newbie in PHP and just starting to learn, so your help will be highly appreciated.

Hey guys - me again!

I have a discount box, where i wish to check two "if" queries.  These are i) the 'uniquecode' and ii) the 'uses' - so a code can only be used a set amount of times.

1) How do i state that question in php, bearing in mind the "if uses > 0" needs to relate to the same row as the unique code entered?

2) Also, i have stored the session price in $_SESSION['sessionprice'] - is this the best way to do this, and if not how should i store the current price. 

3) Lastly, and how do I do the php mathematics of "$_SESSION['sessionprice'] minus the discount" - again bearing in mind the "discount" needs to relate to the same row as the unique code entered? (as different codes will have different discounts).


This is how far i've gotten:

Code: [Select]
<?php
//connection settings bla bla bla

$uniquecode = $_POST['discountcode'];

mysql_connect($localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$discountcodecheck = mysql_query("SELECT uniquecode FROM discounttable WHERE uniquecode = '".$uniquecode."'");
$remainingusescheck = mysql_query("SELECT uses FROM discounttable WHERE uniquecode = '".$uniquecode."'");

if (mysql_num_rows($discountcodecheck) > 0) and if (mysql_num_rows($remainingusescheck) > 0)  // <-- this "and if" doesn't work
{
// discount calculation:
// session price minus the unique code's corresponding discount
// update $_session['discountammount']

}
else
{
?><script type="text/javascript">
alert("Discount Code Entered Is Either Not Valid Or Has Been Previously Used.");
        history.back();
      </script><?php
}

// rest of code and close connection
?>

Any help or comments will, as always be politely welcomed and appreciated

Cheers,

Tom.

Hi,

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

The first query goes through but the second does not just seeing if I'm missing something.

<?php

// Include the database page
require ('../inc/dbconfig.php');

if (isset($_POST['submittitle'])) {
    $titlename = mysqli_real_escape_string($dbc, $_POST['titlename']);
    $titleshortname = mysqli_real_escape_string($dbc, $_POST['titleshortname']);
    $titlestyle = mysqli_real_escape_string($dbc, $_POST['titlestyle']);
    $titlestatus = mysqli_real_escape_string($dbc, $_POST['titlestatus']);

    $query = "INSERT INTO `titles` (titlename, titleshortname, style_id, status_id, creator_id, datecreated) VALUES ('$titlename','$titleshortname','$titlestyle','$titlestatus', 1, NOW())";
    mysqli_query($dbc, $query);
    $query_id = mysqli_insert_id($dbc); 
    $query1 = "INSERT INTO `champions` (title_id) VALUES ('$query_id')";
    mysqli_query($dbc, $query1);  
}
?>

Hi all,

I've built a few amateur sites in the past but am keen to utilize php combined with mysql for my current project.  However, I have little knowledge of php and am just picking it up as I go (same way I've done for all my computer knowledge)

What I'm looking for may sound simple, but I really don't know where to start and I'm hoping there are some kind souls here that will lend a hand.  I've got some fantastic help here before and am hoping I'll get lucky again.

SQL Database
meetingid venue
001 new york
002 chicago
003 new york
004 new york
005 new york
006 texas
007 texas
008 chicago
009 new york
010 new york

Question
If I hard code everything around it, I'm just looking for the code I would need to have it display as follows: (hard coded text in blue)

"New York has been used * times." 
(Where * would count the number of times "New York" appears in the venue column of the database.)
"The most consecutive times New York has been used is % ." 
(Where % would count the maximum number of consecutive times "New York" appears in the venue column of the database.)

Giving the output:
New York has been used 6 times.
The most consecutive times New York has been used is 3.

UPDATE: If I remove lastedit = NOW() from the query, and just leave SET content = $var, it works. :/

If already double checked to see if a user would make it to this point in the code, and they do. And the variable $pid IS assigned a value. I've also done a quick check and changed the UPDATE queries to SELECT queries, and then echoed out the number of rows and it returned a successful result. Yet, people still can't seem to have their posts updated...why?

My code:

//if it's a thread, update the thread, if it's a post, update the post
if($type == 2)
{
//update thread
mysql_query("UPDATE threads SET lastedit = NOW() AND content = '{$content}' WHERE id = {$id}") or die(mysql_error());

//send them to their post
redirect('viewthread.php?forum='. $forum_id .'&id='. $id .'&page='. $page);
}
else
{
//update post
mysql_query("UPDATE posts SET lastedit = NOW() AND content = '{$content}' WHERE id = {$pid}") or die(mysql_error());

//send them to their post
redirect('viewthread.php?forum='. $forum_id .'&id='. $id .'&page='. $page .'&post='. $post);
}

I've been playing around with PDO lately, and I've been trying to get use to the basic functions as I've mentioned in some of my other posts/questions. Recently I switched to the ? bind/token (can't remember it's called), so I reformatted my processQuery. I've also tried to add in a fetching option in the method, but I can't seem to get anything to work.

I've looked up several internet tutorials but it seems like I'm doing everything the same. Yet, the query doesn't seem to run. :/

$database->processQuery2("INSERT INTO test (test, testa) VALUES (?, ?)", array('noob', 'newb2'), false);

Now for my method:


public function processQuery2($query, array $binds, $fetch)
    {
        $query_handle = $this->dbc->prepare($query);
        
        if(count($binds) > 0)
        {
            $i = 1;
            $value = array();
            
            foreach($binds as $bind)
            {
                $query_handle->bindParam($i, $value[$i]);
                $value[$i] = $bind;
                $i++;
            }
        }
        $query_handle->execute();
        
        //if($fetch == true)
        //{
            //return $query_handle->fetchAll();
        //}
    }


Any reasons to why it's not working? No errors are outputted, either.

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.