Angeleyezz Posted October 5, 2012 Share Posted October 5, 2012 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)) { ?> Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/ Share on other sites More sharing options...
ManiacDan Posted October 5, 2012 Share Posted October 5, 2012 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 Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1382983 Share on other sites More sharing options...
Angeleyezz Posted October 5, 2012 Author Share Posted October 5, 2012 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. Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1382984 Share on other sites More sharing options...
ManiacDan Posted October 5, 2012 Share Posted October 5, 2012 So "type" is in both tables? Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1382985 Share on other sites More sharing options...
Angeleyezz Posted October 5, 2012 Author Share Posted October 5, 2012 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. Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1382987 Share on other sites More sharing options...
ManiacDan Posted October 5, 2012 Share Posted October 5, 2012 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 Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1382988 Share on other sites More sharing options...
Barand Posted October 5, 2012 Share Posted October 5, 2012 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. Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1382989 Share on other sites More sharing options...
ManiacDan Posted October 5, 2012 Share Posted October 5, 2012 If we're just critiquing the code in general, you're also very vulnerable to SQL injection. Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1382990 Share on other sites More sharing options...
Angeleyezz Posted October 5, 2012 Author Share Posted October 5, 2012 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 Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1382991 Share on other sites More sharing options...
ManiacDan Posted October 5, 2012 Share Posted October 5, 2012 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. Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1382993 Share on other sites More sharing options...
Angeleyezz Posted October 5, 2012 Author Share Posted October 5, 2012 do you think i can actually do this through a union? Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1382994 Share on other sites More sharing options...
Angeleyezz Posted October 5, 2012 Author Share Posted October 5, 2012 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. Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1382996 Share on other sites More sharing options...
ManiacDan Posted October 5, 2012 Share Posted October 5, 2012 There doesn't appear to be a relationship between these tables. Is fuel_code the same as expense_categories.id? Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1382997 Share on other sites More sharing options...
Angeleyezz Posted October 5, 2012 Author Share Posted October 5, 2012 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 Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1382999 Share on other sites More sharing options...
Angeleyezz Posted October 5, 2012 Author Share Posted October 5, 2012 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? Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1383000 Share on other sites More sharing options...
Jessica Posted October 5, 2012 Share Posted October 5, 2012 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. Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1383001 Share on other sites More sharing options...
Barand Posted October 5, 2012 Share Posted October 5, 2012 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 Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1383002 Share on other sites More sharing options...
ManiacDan Posted October 5, 2012 Share Posted October 5, 2012 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 Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1383006 Share on other sites More sharing options...
Angeleyezz Posted October 5, 2012 Author Share Posted October 5, 2012 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'); ?> Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1383007 Share on other sites More sharing options...
Barand Posted October 5, 2012 Share Posted October 5, 2012 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. Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1383009 Share on other sites More sharing options...
Angeleyezz Posted October 5, 2012 Author Share Posted October 5, 2012 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. Link to comment https://forums.phpfreaks.com/topic/269128-2-querys-1-while-loop-possible/#findComment-1383010 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.