Hi there, I'm struggling to get the following PHP/MySQL function to display the correct info and would appreciate some input on where I'm going wrong. The function displays a list of accounts from one MySQL table then displays dates from another table using the field "terms_code" as a link. The list of accounts is displayed however the dates from the other table are not. I've already spent far too long on this and I'm sure it's going to be a simple oversight. <?php function show_accounts_with_grouped_terms() { $retval = ''; $retval .= '<p class="sub-title">Customer Accounts with Grouped Terms</p>'; $retval .= '<div class="column">'; $retval .= '<table class="full-blue" cellspacing="0">'; $retval .= '<thead>'; $retval .= '<tr>'; $retval .= '<th>A/C</th>'; $retval .= '<th>Description</th>'; $retval .= '<th>Terms Code</th>'; $retval .= '<th>P1 From</th>'; $retval .= '<th>P1 To</th>'; $retval .= '<th> </th>'; $retval .= '<th>P2 From</th>'; $retval .= '<th>P2 To</th>'; $retval .= '<th> </th>'; $retval .= '<th>Fixed Until</th>'; $retval .= '<th colspan="2"> </th>'; $retval .= '</tr>'; $retval .= '</thead>'; $retval .= '<tbody>'; $date = date("d/m/Y"); $sql = mysql_query("SELECT * FROM cf_terms_groups ORDER BY account_name") or die(mysql_error()); while($row = mysql_fetch_array($sql)) { $rs = mysql_query("SELECT * FROM cf_terms_headers WHERE terms_code = '".$row["terms_code"]."'") or die(mysql_error()); $data = mysql_fetch_assoc($rs) or die(mysql_error()); $retval .= '<tr>'; $retval .= '<td align="center">'; $retval .= '<form target="_blank" action="pages/price-list-print-tg.php" method="post">'; $retval .= '<input type="hidden" name="account_number" value="'.$row["account_number"].'">'; $retval .= ''.$row["account_number"].'</td>'; $retval .= '<td>'.$row["account_name"].'</td>'; $retval .= '<td>'.$row["terms_code"].'</td>'; $retval .= '<td align="center">'.$data["p1_from"].'</td>'; $retval .= '<td align="center">'.$data["p1_to"].'</td>'; $retval .= '<td align="center"><input type="radio" name="period" value="1"></td>'; $retval .= '<td align="center">'.$data["p2_from"].'</td>'; $retval .= '<td align="center">'.$data["p2_to"].'</td>'; $retval .= '<td align="center"><input type="radio" name="period" value="2"></td>'; $retval .= '<td align="center">'.$data["fixed_until"].'</td>'; $retval .= '<td align="center"> </select></td>'; $retval .= '<td align="center"><input type="submit" value=" Print " name=""></form></td>'; $retval .= '</tr>'; $retval .= '</tbody>'; $retval .= '</table>'; } $retval .= '</div>'; print $retval; } ?> PHP:
Well... you could relearn how to use quotes, and perhaps stop using mysql_ as it's deprecated, and has been for many years. Go for mysqli_ or PDO instead. But, you could try something like $rs = mysql_query = ("SELECT * FROM cf_terms_headers WHERE terms_code = '$row['terms_code']'") or die(mysql_error()); PHP: You can also look at errormessages, if there are any, run the query in PHPMyAdmin or similar to see if actually works, etc.
Well... Thanks for your kind comments. I tried what you suggested but unfortunately it didn't work. As expected it generated a syntax error. My understanding of how to use quotes has served me well for my last 10 years of general scripting. Did you not mean this? $rs = mysql_query = ("SELECT * FROM cf_terms_headers WHERE terms_code = '".$row['terms_code']."'") or die(mysql_error()); PHP:
change while($row = mysql_fetch_array($sql)) { PHP: to while($row = mysql_fetch_assoc($sql)) { PHP: Also you should not be using the mysql functions any more. They will be deprecated at some point. use mysqli or pdo instead
Hi, I've also tried using your suggestion but to no success. This is for an intranet at my work place and any software changes would take an age to get approved. Regards
Always start with SINGLE QUOTES and Then work in with DOUBLE. $rs = mysql_query('SELECT * FROM `cf_terms_headers` WHERE `terms_code` = "' . $row['terms_code'] . '"') or die(mysql_error()); PHP: Also, do not put a = after mysql_query. Can't believe nobody caught that.
I may be missing something really obvious here, but why not just join the tables in the MySQL query, then spew out the results in one go, instead of using two queries? As in something like: SELECT a.account_name, a.some_other_field, b.date FROM cf_terms_groups a JOIN cf_terms_headers b ON a.terms_code = b.terms.code WHERE ....
The = was my bad, editing on a cell phone and didn't catch it. However, there's no reason to concat an SQL query in PHP. You can just put the PHP variables directly in single quotes, and they will work just fine.
No. You don't need to concat an SQL query in PHP. However, there was an error in my suggestion, there was an = between mysql_query and the statement.
My suggestion regarding quotes was for ease of use and reading of code, not necessity. It is wise to use single quotes outside and then double inside, it prevents having to backslash double quotes in most cases, saving time and making more professional looking code.
How does double quotes become literal inside an SQL-query? If they are, you're having some serious problems with your DB-code. The point is that concat'ing strings takes more time than just parsing the variables natively, hence you shouldn't be doing it, since it will affect the run-time. When you're doing thousands of queries, that makes a difference, even it's only fractions of microseconds. For readability, sure, it might be better concat'ing, but again, I don't really see how this: query("SELECT * FROM table WHERE id = '$id' AND username = '$username'"); PHP: is less readable than query('SELECT * FROM table WHERE id = '.$id.' AND username = '.$username.''); PHP: Besides, variables shouldn't be in the query anyway, at least pulled directly. Prepared statements eliminates any need for quotes: query("SELECT * FROM table WHERE id = :id AND username = :username"); PHP:
As @PoPSiCLe said, you need to get out of the outdated mysql_ thinking - and that includes dumping values into your query string as they have NO business being there if you care about security or efficiency; particularly if you were to run that same query with different inputs more than once. .. Likewise, mein gott your markup needs help.... a paragraph doing a numbered heading or CAPTION's job, attributes like align that have no business in any markup written after 1997, wasting memory dumping everything into variables when all you're doing is printing it? Also, you can't put a form inside a table / around the TD like that, completely invalid and gibberish markup. The form needs to go around the table, not the other way around. Much less the target attribute that doesn't even exist on FORM, and has no business in a non-frameset page any time after 1997. If you want each item in it's own form, you're going to need to make a table for each one, or not use tables. As to a query, given how you're bombing out, I'd probably use something more like this: SELECT * FROM cf_terms_groups AS groups, cf_terms_headers AS headers WHERE groups.terms_code = headers.terms_code ORDER BY groups.account_name Code (markup):