2 Querys, 1 While Loop, Possible?

[Angeleyezz]

I'm trying to put 2 seperate querys that are related in the same while loop output, is this even possible or am i just not doing it right.

 

// Query & assign to the $client_information table.
$get_last_transaction = "SELECT * FROM expense_fuel ORDER BY id DESC LIMIT 1" or die (mysql_error());
$transaction = mysql_query($get_last_transaction) or die (mysql_error());

// Get fuel type name from the fuel code
$get_fuel_name = "SELECT * FROM expense_categories WHERE $fuel_code = type";
$fuel_name_result = mysql_query($get_fuel_name) or die (mysql_error());

while ($row = mysql_fetch_array($transaction))
{
?>

[ManiacDan]

How are they related? Can you JOIN these two tables?

 

Your syntax is wrong in the second query, you can't just dump a raw PHP variable into a mysql query

[Angeleyezz]

I should have posted all of the code, the $fuel_code is carried over from the form and defined already. The type is the relational point between both tables. its defined by a code from the form, then i need to match the code to the other table and grab the type name itself from the list.

[ManiacDan]

So "type" is in both tables?

[Angeleyezz]

expense_categories has type

expense_fuel has fuel_code

 

the fuel_code is carried over from the form, i need to match the code to the expense_categories table and print the type name basically

 

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

// Load Header Include  
include('includes/header.php');

// Grab elements from previous form (expendeture_add_fuel.php
$fuel_code = $_POST['fuel_code'];
$fuel_amount = $_POST['fuel_amount'];
$fuel_month = $_POST['fuel_month'];
$fuel_day = $_POST['fuel_day'];
$fuel_year = $_POST['fuel_year'];

// Select the Operations Data-Base
mysql_select_db("terra_elegante_operations", $con);

// Insert the form data into the DB data table
$sql="INSERT INTO expense_fuel (fuel_code, amount, month, day, year)
VALUES
('$fuel_code', '$fuel_amount', '$fuel_month', '$fuel_day', '$fuel_year')";

if (!mysql_query($sql,$con))
 {
 die('Error: ' . mysql_error());
 }

// Query & assign to the $client_information table.
$get_last_transaction = "SELECT * FROM expense_fuel ORDER BY id DESC LIMIT 1" or die (mysql_error());
$transaction = mysql_query($get_last_transaction) or die (mysql_error());

// Get fuel type name from the fuel code
$get_fuel_name = "SELECT * FROM expense_categories WHERE $fuel_code = type";
$fuel_name_result = mysql_query($get_fuel_name) or die (mysql_error());

while ($row = mysql_fetch_array($transaction))
{
?>
<br />
<table border="0" cellspacing="0" cellpadding="3" width="100%">
 <tr>
   <td align="center"><font face="verdana" size="3" color="#000000"><b>1 RECORD ADDED</b></font></td>
 </tr>
</table>
<br />
<table border="1" cellspacing="0" cellpadding="3" width="100%" bordercolor="#000000">
 <tr bgcolor="#333333" align="center">
   <td width="20%"><font face="verdana" size="2" color="#FFFFFF">TRANSACTION ID</font></td>
   <td width="20%"><font face="verdana" size="2" color="#FFFFFF">CODE</font></td>
   <td width="20%"><font face="verdana" size="2" color="#FFFFFF">TYPE</font></td>
   <td width="20%"><font face="verdana" size="2" color="#FFFFFF">AMOUNT</font></td>
   <td width="20%"><font face="verdana" size="2" color="#FFFFFF">DATE</font></td>
 </tr>
 <tr>
   <td><font face="verdana" size="1" color="#000000"><?php echo $row['id']; ?></font></td>
   <td><font face="verdana" size="1" color="#000000"><?php echo $row['fuel_code']; ?></font></td>
   <td><font face="verdana" size="1" color="#000000"><?php echo $row['type']; ?></font></td>
   <td><font face="verdana" size="1" color="#000000">$<?php echo $row['amount']; ?></font></td>
   <td><font face="verdana" size="1" color="#000000"><?php echo $row['month']; ?>-<?php echo $row['day']; ?>-<?php echo $row['year']; ?></font></td>
 </tr>
</table>

<br />
<font face="verdana" size="2"><b><a class="bubble_nav" href="index.php">RETURN HOME</a> | <a class="bubble_nav" href="expendeture_add_expense.php">ADD ANOTHER EXPENSE</a></b></font>

<br />
<br />
<br />
<br />
<br />
<br />

<?php
}

include('includes/footer.php');

?>

 

this is where i'm at so far.

[ManiacDan]

You're not making any sense to me. Sow a few rows of data from the tables, and what you want the results to look like, assuming you already have a fuel code

[Barand]

It sounds like the join you need is

SELECT f.amount, f.month, f.day, f.year, c.fuel_name
FROM expense_fuel f
INNER JOIN expense_categories c ON f.fuel_code = c.type

 

A note on your insert queries - numeric values such as fuel_amount should not be quoted.

 

Also, store your dates as DATE type columns (YYYY-MM-DD) and not separate day, month, year columns.

[ManiacDan]

If we're just critiquing the code in general, you're also very vulnerable to SQL injection.

[Angeleyezz]

im looking at the join that barand gave me, trying to make it work, i think hes onto the right path, but im having some trouble implamenting it. kinda rusty with php i havnt messed with it since december when i first built this program, now im trying to add a few things to it =( should have kept going back then when i was more into the knowledge of it lol

[ManiacDan]

Well if you'd answer the questions I've asked, we could help you more.

 

Show the table structure, show the data, show what you want it to look like.

 

Barand and I can continue guessing randomly all day, or you can actually show us the problem you're asking us to solve.

[Angeleyezz]

do you think i can actually do this through a union?

[Angeleyezz]

Ok, I have 2 tables,

 

Table 1 - expense_fuel

fields = id, fuel_code, amount, month, day, year

 

Table 2 - expense_categories

fields = id, type

 

 

the form on the page you select the type, and it adds the code to the expense_fuel table, now what i need to do is refrence the expense_categories table to the expense_fuel table and grab the name of the fuel type from the code that was assigned from the form.

[ManiacDan]

There doesn't appear to be a relationship between these tables. Is fuel_code the same as expense_categories.id?

[Angeleyezz]

yes its assigned and defined once in the expense_categories table, its acting as kinda a sku table of contents for all expenses, the code is inserted into the expense_fuel table already from the form. now i just need to refrence the code number and pull the name of it from the row fo the expense_category. so i can show it on my output

Edited October 5, 2012 by Angeleyezz

[Angeleyezz]

actually this might be easier if i just did 2 completely seperate querys on this, and assigned each row a variable then just wrote the output without a while loop. for this im only pulling 1 record, witch is the last one entered. you think that would work a bit better in this case?

[Jessica]

Well if you'd answer the questions I've asked, we could help you more.

 

Show the table structure, show the data, show what you want it to look like.

 

Barand and I can continue guessing randomly all day, or you can actually show us the problem you're asking us to solve.

[Barand]

UNION? No.

 

Try

SELECT f.amount, f.month, f.day, f.year, c.type as fuel_name
FROM expense_fuel f
INNER JOIN expense_categories c ON f.fuel_code = c.id

[ManiacDan]

If they're the same, you can JOIN the tables:

 

 

SELECT * 
FROM expense_fuel
JOIN expense_categories ON expense_fuel.fuel_code = expense_categories.id
WHERE fuel_code = '{$type}'

You're still vulnerable to SQL Injection

Edited October 5, 2012 by ManiacDan

[Angeleyezz]

I figured it out

i just ran 2 seperate querys, assigned the result sets to their own row variables. then echoed a mix of both tables on the output.

 

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

// Load Header Include  
include('includes/header.php');

// Grab elements from previous form (expendeture_add_fuel.php)
$fuel_code = $_POST['fuel_code'];
$fuel_amount = $_POST['fuel_amount'];
$fuel_month = $_POST['fuel_month'];
$fuel_day = $_POST['fuel_day'];
$fuel_year = $_POST['fuel_year'];

// Select the Operations Data-Base
mysql_select_db("terra_elegante_operations", $con);

// Insert the form data into the DB data table
$sql="INSERT INTO expense_fuel (fuel_code, amount, month, day, year)
VALUES
('$fuel_code', '$fuel_amount', '$fuel_month', '$fuel_day', '$fuel_year')";

if (!mysql_query($sql,$con))
 {
 die('Error: ' . mysql_error());
 }

// Query & assign to the $client_information table.
$get_last_transaction = mysql_query("SELECT * FROM expense_fuel ORDER BY id DESC LIMIT 1");
$row = mysql_fetch_array($get_last_transaction);
$id = $row['id'];
$code = $row['fuel_code'];
$amount = $row['amount'];
$month = $row['month'];
$day = $row['day'];
$year = $row['year'];


// Get fuel type name from the fuel code
$get_fuel_name = mysql_query("SELECT * FROM expense_categories WHERE $fuel_code = id");
$row = mysql_fetch_array($get_fuel_name) or die (mysql_error());
$fuel_type = $row['type'];
?>
<br />
<table border="0" cellspacing="0" cellpadding="3" width="100%">
 <tr>
   <td align="center"><font face="verdana" size="3" color="#000000"><b>1 RECORD ADDED</b></font></td>
 </tr>
</table>
<br />
<table border="1" cellspacing="0" cellpadding="3" width="100%" bordercolor="#000000">
 <tr bgcolor="#333333" align="center">
   <td width="20%"><font face="verdana" size="2" color="#FFFFFF">TRANSACTION ID</font></td>
   <td width="20%"><font face="verdana" size="2" color="#FFFFFF">CODE</font></td>
   <td width="20%"><font face="verdana" size="2" color="#FFFFFF">TYPE</font></td>
   <td width="20%"><font face="verdana" size="2" color="#FFFFFF">AMOUNT</font></td>
   <td width="20%"><font face="verdana" size="2" color="#FFFFFF">DATE</font></td>
 </tr>
 <tr>
   <td><font face="verdana" size="1" color="#000000"><?php echo $id; ?></font></td>
   <td><font face="verdana" size="1" color="#000000"><?php echo $code; ?></font></td>
   <td><font face="verdana" size="1" color="#000000"><?php echo $fuel_type; ?></font></td>
   <td><font face="verdana" size="1" color="#000000">$<?php echo $amount; ?></font></td>
   <td><font face="verdana" size="1" color="#000000"><?php echo $month; ?>-<?php echo $day; ?>-<?php echo $year; ?></font></td>
 </tr>
</table>

<br />
<font face="verdana" size="2"><b><a class="bubble_nav" href="index.php">RETURN HOME</a> | <a class="bubble_nav" href="expendeture_add_expense.php">ADD ANOTHER EXPENSE</a></b></font>

<br />
<br />
<br />
<br />
<br />
<br />

<?php

include('includes/footer.php');

?>

[Barand]

I can give you some more advice for free:

 

Totally disregarding all the help we've been trying to give you, and thereby wasting our time (free) is not the best way to get help in the future. Goodbye.

[Angeleyezz]

its not that i was dis reguarding it, i just am not that knowlegable about what you were trying to show me, i really didnt understand it at all. im still pretty new and rusty at php, and im trying to learn as i go along. i do really appreciate the help you guys have tried to give me, i just dont understand how you were trying to do it at all. and i posted what i did to show you what i was trying to do and how i made it work. now you can see what i was trying to achieve and if how you were trying to show me could be the same way, awsome im still checking out how you were trying to show me.

 

i wasnt trying to waste anyones time.