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)) { ?> Quote 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 Quote 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. Quote 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? Quote 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. Quote 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 Quote 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. Quote 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. Quote 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 Quote 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. Quote 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? Quote 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. Quote 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? Quote 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 (edited) 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 Quote 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? Quote 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. Quote 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 Quote 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 (edited) 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 Quote 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'); ?> Quote 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. Quote 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. Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.