Collect from 2 datatable in 1 querry

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
Goofan
Forum Contributor
Posts: 305
Joined: Wed Nov 04, 2009 2:11 pm
Location: Sweden

Collect from 2 datatable in 1 querry

Post by Goofan »

Hi i got a problem that i cant manage to get around...

i got this code and im trying to collect from 2 datatables and use them in 1 querry
look int the text for
-------------->
MY PROBLEM
<--------------

Code: Select all

 
<?php
    // Get the database connector stuff
    include "../login/database.php";
     
    // Build our query
    $sql1 = "SELECT * FROM infantries";
     
    // Get the result if there is one
   // DO NOT die() IN PRODUCTION!!!
   if (!$result1 = mysql_query($sql1))
    {
     die('The query<br /><strong>' . $sql1 . '</strong><br />failed:<br />' . mysql_error());
    }
?>
<?php
 
 
  $id =(isset($_GET['saved_id'])) ? (int)$_GET['saved_id'] : false;
    if($id !== false)
 {
    $sql="SELECT * FROM konto WHERE saved_id=$id";      //selecting all from DB "Konto" where saved_id is the same as in the array $id
 }
    else
 {
    echo "NO saved_id!";
 }
$result = mysql_query($sql) or die(mysql_error());//Välj all info i tall. //hämtar all info från tabell
                 
while($row = mysql_fetch_array( $result )) //hämtar info från tabell.
 
{
    
    $pengar=$row['pengar'];
    $my['Swordmen'] = $row['swordmen'];
    $my['Macemen'] = $row['macemen'];
    $my['Pikemen'] = $row['pikemen'];
 
}
echo "<p>$pengar</p>";
 
if(isset($_POST['Swordmen']))
{
        // the Swordmen Buy button was clicked
        $unit = "swordmen";
        $unit2 = "Swordmen";
        $qty = $_POST['qty_Swordmen'];
}
if(isset($_POST['Macemen']))
{
        // the Macemen Buy button was clicked
        $unit = "macemen";
        $unit2 = "Macemen";
        $qty = $_POST['qty_Macemen'];
}  
if(isset($_POST['Pikemen']))
{
        // the Pikeman Buy button was clicked - so you need to use qty_Pikemen
        $unit = "pikemen";
        $unit2 = "Pikemen";
        $qty = $_POST['qty_Pikemen'];
}
if(isset($unit) && is_numeric($qty) && $qty > 0)
{ // if isset($unit) then one of the three buttons was clicked
    $sql="UPDATE konto SET $unit=$unit + $qty WHERE saved_id=$id";//Sätt upp SQL fråga.
    $sql="UPDATE konto SET $pengar - $Buyfor WHERE saved_id=$id";//Sätt upp SQL fråga.              
 
<--------------
MY PROBLEM
---------------
i need it to say $pengar(containing 800 000) - $Buyfor(containing the price of the thing)
so that if the costumer buys a thing for 300 it will become 799 700
 
'This it cant find ($Buyfor) i know that in my database infantries it exists
$pengar is in the second datatable and it is also existing
-------------->
    $result = mysql_query($sql) or die(mysql_error());//Välj all info i tabell.
if(mysql_affected_rows()>0)
 {
     $my[$unit2] += $qty;
 }
 
}
 
 
?>
 
 
 
 
then i got some more code further down the page:
this is only one page....

Code: Select all

 
 <table border="1">
   <tr>
    <th>Units</th>
        <th>AtkArm</th>
    <th>Buyfor</th>
        <th>Sellfor</th>
    <th>You Own</th>
        <th>Buy</th>
   </tr>
<form action="#" method="post">
 <?php while ($row1 = mysql_fetch_assoc($result1)): ?>
 <tr>
        <th><?php echo $row1['Units'] ;?></th>
    <th><?php echo $row1['AtkHp'] ;?></th>
        <th><?php echo $row1['Buyfor'] ;?></th>
        
    <th><?php echo $row1['Sellfor'] ;?></th>
        <th><?php echo $my[$row1['Units']]; ?></th>
    <th>
        <font color="black" valign= "top"><input style='width:45;height:20' type="text" name="qty_<?php echo $row1['Units']?>" size="250" class="textbox" value=""></font>
        <input style='width:45;height:30;font-weight:bold' name="<?php echo $row1['Units']?>" type="submit" class="submit" value="Buy!">
    </th>
   </tr>
 <?php endwhile; ?>
 </form>
 </table>
 
User avatar
Goofan
Forum Contributor
Posts: 305
Joined: Wed Nov 04, 2009 2:11 pm
Location: Sweden

Re: Collect from 2 datatable in 1 querry

Post by Goofan »

what i want it to do is more or less like this piece of code "dont work"

Code: Select all

 
<?php
  $id =(isset($_GET['saved_id'])) ? (int)$_GET['saved_id'] : false;
    if($id !== false)
 {
    $sql="SELECT * FROM konto WHERE saved_id=$id";      //selecting all from DB "Konto" where saved_id is the same as in the array $id
 }
    else
 {
    echo "NO saved_id!";
 }
 
 
 
    $sql1 = "SELECT * FROM infantries";
 
 
 
?>
 
so that i can collect from both datatables...
in one and same <?php-----?>
if u understand plz give a hand :D
if u dont then ask :D


-Thanks in advance
-Thomas
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Re: Collect from 2 datatable in 1 querry

Post by daedalus__ »

thomas, collect what from both databases?

i dont understand what you are trying to achieve.

but usually when im designing sql queries i have a rule, if i can't describe the data i want to pull in a single sql like sentence, i start over.

there are different ways to to pull data from multiple tables and they may differ depending on the dialect of sql.

in mysql you can say something like "SELECT data FROM table_one, table_two WHERE condition = 'http://www.youtube.com/watch?v=6yz67CpesW0'" or use the JOIN keyword.
User avatar
Goofan
Forum Contributor
Posts: 305
Joined: Wed Nov 04, 2009 2:11 pm
Location: Sweden

Re: Collect from 2 datatable in 1 querry

Post by Goofan »

ok so ill start over with the explaning:
ok so i got 2 databases one called Units and one called infantries and as one got "saved_id" i cant combine the two. now i wan to collect "Buyfor" (datatable) from the database infantries. Then i wanna collect from the database called Units where i specify that (at the saved_id) i get how many a user has orderd or owning the "product" (datatable).
now the problem is i wanna get so that i can say:
if textbox 1 containes the value 7 then thats the amount he wanna order.
Code wise it should be something like this:
if money >= product then
{
echo "u can buy this"
then here i uppdate the datatable (money) and say minus and the buyfor so like money - buyfor <-----this is where i cant make it work
(buyfor standing for the price of the product and money is what u got)
else
echo "u cant"
and here nothing happens
}
now in this case textbox1 will be the product that is orderd and the product would be the money that the user got (online).
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Re: Collect from 2 datatable in 1 querry

Post by daedalus__ »

okay let me try to understand this

Code: Select all

 
$q = "select price from infantry where id = 'id'";
$r = mysql_query($q);
$price = mysql_result($r,0);
 
$price * $quantity = $product_price;
 
$q = "select money from users"
$r = mysql_query("$q");
$money = mysql_result($r,0);
 
if ($money >= $product_price)
{
    "buy"
}
else
{
    "don't"
}
 
???????

if thats wha tyou are trying to do then you dont need to join data from two tables just run two separate queries. unless you want to do the comparison with SQL :|
User avatar
Goofan
Forum Contributor
Posts: 305
Joined: Wed Nov 04, 2009 2:11 pm
Location: Sweden

Re: Collect from 2 datatable in 1 querry

Post by Goofan »

sorry should change all databases to datatables and all datatables to datacells :D sorry
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Re: Collect from 2 datatable in 1 querry

Post by daedalus__ »

it's okay. we can talk through code. :)
User avatar
Goofan
Forum Contributor
Posts: 305
Joined: Wed Nov 04, 2009 2:11 pm
Location: Sweden

Re: Collect from 2 datatable in 1 querry

Post by Goofan »

ohh well thats actually looks pritty nice necer thought of it that way :D
allways glad to have a pair of extra ayes sometimes :D


-Thanks
-Thomas
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Re: Collect from 2 datatable in 1 querry

Post by daedalus__ »

word. that code isn't complete though. its sort-of pseudo-code to describe the idea. there is a lot to add to it. the mysql connection, error handling, security, etc.
User avatar
Goofan
Forum Contributor
Posts: 305
Joined: Wed Nov 04, 2009 2:11 pm
Location: Sweden

Re: Collect from 2 datatable in 1 querry

Post by Goofan »

Ok that code worked perfectly however i get a small error when i insert it (with a few modification) into my code.


code is:

Code: Select all

 
<?php
    // Get the database connector stuff
    include "../login/database.php";
     
    // Build our query
    $sql1 = "SELECT * FROM Units";
     
    // Get the result if there is one
   // DO NOT die() IN PRODUCTION!!!
   if (!$result1 = mysql_query($sql1))
    {
     die('The query<br /><strong>' . $sql1 . '</strong><br />failed:<br />' . mysql_error());
    }
?>
<?php
 
 
  $id =(isset($_GET['saved_id'])) ? (int)$_GET['saved_id'] : false;
    if($id !== false)
 {
    $sql="SELECT * FROM konto WHERE saved_id=$id";      //selecting all from DB "Konto" where saved_id is the same as in the array $id
 }
    else
 {
    echo "NO saved_id!";
 }
$result = mysql_query($sql) or die(mysql_error());//Välj all info i tall. //hämtar all info från tabell
                 
while($row = mysql_fetch_array( $result )) //hämtar info från tabell.
 
{
    
    $pengar = $row['pengar'];
    $my['Swordmen'] = $row['swordmen'];
    $my['Macemen'] = $row['macemen'];
    $my['Pikemen'] = $row['pikemen'];
 
}
echo "<p>$pengar</p>";
 
if(isset($_POST['Swordmen']))
{
        // the Swordmen Buy button was clicked
        $unit = "swordmen";
        $unit2 = "Swordmen";
        $qty = $_POST['qty_Swordmen'];
}
if(isset($_POST['Macemen']))
{
        // the Macemen Buy button was clicked
        $unit = "macemen";
        $unit2 = "Macemen";
        $qty = $_POST['qty_Macemen'];
}  
if(isset($_POST['Pikemen']))
{
        // the Pikeman Buy button was clicked - so you need to use qty_Pikemen
        $unit = "pikemen";
        $unit2 = "Pikemen";
        $qty = $_POST['qty_Pikemen'];
}
if(isset($unit) && is_numeric($qty) && $qty > 0)
{ // if isset($unit) then one of the three buttons was clicked
    
    
$sql = "select buyfor from units ";
$result = mysql_query($sql);
$buyfor = mysql_result($result,0);
 
 
$product_price = $buyfor * $qty;
 
 
if ($pengar >= $product_price)
{
   echo "You have succesfully bought $qty $unit";
    $sql="UPDATE konto SET $unit=$unit + $qty WHERE saved_id=$id";//Sätt upp SQL fråga.
    $sql="UPDATE konto SET $pengar=$pengar - $product_price WHERE saved_id=$id";//Sätt upp SQL fråga.
    $result = mysql_query($sql) or die(mysql_error());//Välj all info i tabell.
if(mysql_affected_rows()>0)
 {
     $my[$unit2] += $qty;
 }
 
}
}
 
else
{
   echo "You dont afford that many.";
}
 
 
 
?>
 
 
Error is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '800000=800000 - 18000 WHERE saved_id=2' at line 1
i guess it has to do with my "uppdate sql twice after each other.
I dont get the error if i remove one of the sql´s.
so i tried to make it into one big happy sql uppdate... but it wasnt happy :( (when the code were as before "one sql question" it uppdated however when i combine them "show code under" nothing happends).

My code when i combine the 2:

Code: Select all

 
$sql="UPDATE konto SET $unit=$unit + $qty & $pengar=$pengar - $product_price WHERE saved_id=$id";//Sätt upp SQL fråga.
 
and with this code it adds the $qty to unit and shows it however if the field contains 0 and i put in 12 in the textbox it eraize the next field so that it also becomes 0 (and it dont uppdate the first "it still contains 0")
User avatar
Goofan
Forum Contributor
Posts: 305
Joined: Wed Nov 04, 2009 2:11 pm
Location: Sweden

Re: Collect from 2 datatable in 1 querry

Post by Goofan »

ohh and if i press the second button it eraize the first field.... :(
User avatar
Goofan
Forum Contributor
Posts: 305
Joined: Wed Nov 04, 2009 2:11 pm
Location: Sweden

Re: Collect from 2 datatable in 1 querry

Post by Goofan »

"more detailed"
watch as i "press the button"
Attachments
this is after i pressed the second button
this is after i pressed the second button
what happens1.jpg (21.62 KiB) Viewed 1449 times
this is as i am just about to press button 2...
this is as i am just about to press button 2...
what happens.jpg (22.71 KiB) Viewed 1449 times
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Re: Collect from 2 datatable in 1 querry

Post by daedalus__ »

no, no, no. you forgot to run both queries!

Code: Select all

 
echo "You have succesfully bought $qty $unit";
$sql="UPDATE konto SET $unit=$unit + $qty WHERE saved_id=$id";//Sätt upp SQL fråga.
$result = mysql_query($sql) or die("isn't the best way to handle errors");
 
$sql="UPDATE konto SET $pengar=$pengar - $product_price WHERE saved_id=$id";//Sätt upp SQL fråga.
$result = mysql_query($sql) or die(mysql_error());//Välj all info i tabell.
 
if(mysql_affected_rows()>0)
{
    $my[$unit2] += $qty;
}
 
remember that in php variables are dynamically typed. think about it as if every variable is either null or a string until you tell php otherwise. (e.g. $var = array(), $var = new class() )

it says the error with this line:

Code: Select all

 
$sql="UPDATE konto SET $pengar=$pengar - $product_price WHERE saved_id=$id";
 
try putting the arithmetic in parenthesis

Code: Select all

 
$sql="UPDATE konto SET $pengar = ($pengar - $product_price) WHERE saved_id=$id";
 

oh wait. hey $pengar is set to 80000 but there is no field named 80000 in your table is there?

Code: Select all

 
$sql="UPDATE konto SET pengar = ($pengar - $product_price) WHERE saved_id=$id";
 
User avatar
Goofan
Forum Contributor
Posts: 305
Joined: Wed Nov 04, 2009 2:11 pm
Location: Sweden

Re: Collect from 2 datatable in 1 querry

Post by Goofan »

pengar = money (on swedish)
yes i got a variable called $pengar and im telling it to collect from my datatable where saved_id = id
so it got 80000 in it.
if u look at my code postedd before ull see this at row 34:

Code: Select all

 
   $pengar = $row['pengar'];
 

My first querry is runned alot earlier in the coding.
Just a bit diffrent.
Last edited by Goofan on Sat Nov 28, 2009 7:02 am, edited 2 times in total.
User avatar
Goofan
Forum Contributor
Posts: 305
Joined: Wed Nov 04, 2009 2:11 pm
Location: Sweden

Re: Collect from 2 datatable in 1 querry

Post by Goofan »

plz when talking in php use [code =php] (no spacing) to make it easier to read :D

as of now its "not putting in the second algorism $pengar=$pengar- product_price" and its Nulling the first "you own row". Meaning every time i press button 2 its Nulling you own row 1. and if i press button 3 its Nulling both 1, 2. then if i press the button again its nulling all 3 alogng with its own row. :(

know what might be wrong?


-Thanks in advance
-Thomas
Post Reply