dwperry1

The following code works great: PHP: <?php //connection stuff: $current_id = $_GET['record_id']; $record_id = $current_id; $nextquery= "SELECT * FROM daily_sales WHERE record_id > $current_id ORDER BY record_id ASC LIMIT 1"; $nextresult = mysql_query($nextquery) or die(mysql_error()); while($nextrow = mysql_fetch_array($nextresult)) { $nextid = $nextrow['record_id']; } ?> HTML: <a href="http://www.mywebsite.com/view?record_id=<?php echo $nextid; ?>">Next</a> PROBLEM: I need to swap 'record_date' for 'record_id' in the above code. When I do, I get the following error: 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 'ORDER BY record_date ASC LIMIT 1' at line 1 Does anyone have an idea why this is happening? Any help would be appreciated. view.php

Does anyone know how to associate the date with the file? I have two column names with dates: new_date in the 01/01/2013 format and record_date with the 000-00-00 format. when I run the original code with the record_id, everything works, but I can't seem to get the others to work. Any help would be appreciated. Thanks!

I got the script to work, but I still have the same problem of skipping files because the code is still based on the record_id and not the date associated with the page.

When I click on the links, both give me the following error message. Variable id not defined. Script terminating.

I looked over the code more carefully and changed this: $result = mysql_query($query) or die(mysql_error()); to this: $prevresult = mysql_query($query) or die(mysql_error()); This got rid of the error, but the links don't work. I will look at them next. The code is good if I can make it work. It is a much better approach to the problem. Thanks for giving me something better to work with!

I really do appreciate your quick response and expertise. Line 322 is: $current_record = mysql_fetch_assoc($prevresult); Sorry. The error message says it is not a valid argument, but I'm not experienced enough to know how to find out what is making it not work. Any suggestions?

Thanks for your input. It looks like the code should work, but when I run it I get the following error: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /www/htdocs/applealleycafe.com/books/view.php on line 322

I have the following code allowing me to navigate to the previous or next record in the db. In the code below, I would like to swap 'new_date' (which is in the 000-00-00) format for the existing 'record_id' in the script. I need to navigate via the new_date because it is possible with my program to delete a record and reenter it later for that passed date causing the record_id to be out of sinc with the reord_id. Example: My record_id is auto-increment so if I find a mistake in a past record and go to fix it for that date in the past, my auto-increment would be 235, 236, 237, 568, 239, 240 etc., so I can't use record_id to navigate next and previous. When I use new_date instead of record_id, my error says either that the query was empty, or that Variable id not defined. Script terminating. I would appreciate any help you can give, Thanks! Doug This is also on a web page, so here's my code: php: $current_id = $_GET['record_id']; $record_id = $current_id; $prevquery = "SELECT * FROM daily_sales WHERE record_id < $current_id ORDER BY record_id DESC LIMIT 1"; echo $current_id; $prevresult = mysql_query($prevquery) or die(mysql_error()); while($prevrow = mysql_fetch_array($prevresult)) { $previd = $prevrow['record_id']; } $nextquery = "SELECT * FROM daily_sales WHERE record_id > $current_id ORDER BY record_id ASC LIMIT 1"; $nextresult = mysql_query($nextquery) or die(mysql_error()); while($nextrow = mysql_fetch_array($nextresult)) { $nextid = $nextrow['record_id']; } html: <a href="http://www.mywebsite.com/view.php?record_id=<?php echo $previd; ?>">Previous</a> <a href="http://www.mywebsite.com/view.php?record_id=<?php echo $nextid; ?>">next</a>

Yes, but this would cause the db to drop the digits after the decimal point which defeats my purpose. Since I am not comparing anything, I fail to see the value for my project.

Thanks again! I agree tht it isn't necessary in this case, but I still need it to work with the rest of my code which incorporates the getSQLValueString frnction. You helped me do that and I appreciate it.

smerny, Thanks so much for the code. After slightly altering your code below: function Money($float) { if ($float == 0){ return ""; // if the value is "0" then print nothing "" } else { return sprintf("%01.2f", $float); } } and also altering the brackets in my code for the GetSQLValueString below: GetSQLValueString(Money($_POST['currency_am']), "text"), the input to the database now formats for two digits after the decimal point when there is a value and shows nothing in the field if there is no value when the default is NULL for the field in the database. For those of us who care if our code is protected by using the GetSQLValueString function, this is a valuable solution to a common problem. Thanks again, Doug

OK, I will show you how my code works and you can maybe then help me with my problem: <?php require_once('Connections/mysqli_connect.php'); ?> <?php establish connection then other stuff not related to this issue function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue; switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } function Money($float) { return sprintf("%01.2f", $float); } $editFormAction = $_SERVER['PHP_SELF']; if (isset($_SERVER['QUERY_STRING'])) { $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']); } if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "smartform")) { $insertSQL = sprintf("INSERT INTO daily_sales (record_date, new_date, weekday, currency_am, currency_pm, currency_total, coin_am, coin_pm, coin_total, check_am, check_pm, check_total, cc_am, cc_pm, cc_total, cpo_am_total, cpo_pm_total, cpo_total, gross_sales_am, gross_sales_pm, gross_sales_total, wost_am, wost_pm, wost_total, tst_am, tst_pm, tst_total, cpo_am_1, cpo_pm_1, cpo_shift_total_1, cpo_am_2, cpo_pm_2, cpo_shift_total_2, cpo_am_3, cpo_pm_3, cpo_shift_total_3, gross_sales_am2, gross_sales_pm2, gross_sales_total2, ticket_am, ticket_pm, ticket_total, over_short_am, over_short_pm, over_short_total, cpo_am_item_1, cpo_am_item_2, cpo_am_item_3, server1_name, server2_name, server3_name, server4_name, server5_name, serv1_cc_am, serv1_cc_pm, serv1_cc_total, serv2_cc_am, serv2_cc_pm, serv2_cc_total, serv3_cc_am, serv3_cc_pm, serv3_cc_total, serv4_cc_am, serv4_cc_pm, serv4_cc_total, serv5_cc_am, serv5_cc_pm, serv5_cc_total, serv_cc_am_total, serv_cc_pm_total, serv_cc_total, mtd_sales, ytd_sales, mtd_tax, ytd_tax, serv_cc_total2, mtd_cc, gca_am, gca_pm, gca_total, adstm, save_date) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", GetSQLValueString($_POST['record_date'], "text"), GetSQLValueString($_POST['new_date'], "text"), GetSQLValueString($_POST['weekday'], "text"), GetSQLValueString(Money($_POST['currency_am'], "text")), GetSQLValueString(Money($_POST['currency_pm'], "text")), GetSQLValueString(Money($_POST['currency_total'], "text")), GetSQLValueString(Money($_POST['coin_am'], "text")), GetSQLValueString(Money($_POST['coin_pm'], "text")), GetSQLValueString(Money($_POST['coin_total'], "text")), GetSQLValueString(Money($_POST['check_am'], "text")), GetSQLValueString(Money($_POST['check_pm'], "text")), GetSQLValueString(Money($_POST['check_total'], "text")), GetSQLValueString(Money($_POST['cc_am'], "text")), GetSQLValueString(Money($_POST['cc_pm'], "text")), GetSQLValueString(Money($_POST['cc_total'], "text")), GetSQLValueString(Money($_POST['cpo_am_total'], "text")), GetSQLValueString(Money($_POST['cpo_pm_total'], "text")), GetSQLValueString(Money($_POST['cpo_total'], "text")), GetSQLValueString(Money($_POST['gross_sales_am'], "text")), GetSQLValueString(Money($_POST['gross_sales_pm'], "text")), GetSQLValueString(Money($_POST['gross_sales_total'], "text")), GetSQLValueString(Money($_POST['wost_am'], "text")), GetSQLValueString(Money($_POST['wost_pm'], "text")), GetSQLValueString(Money($_POST['wost_total'], "text")), GetSQLValueString(Money($_POST['tst_am'], "text")), GetSQLValueString(Money($_POST['tst_pm'], "text")), GetSQLValueString(Money($_POST['tst_total'], "text")), GetSQLValueString(Money($_POST['cpo_am_1'], "text")), GetSQLValueString(Money($_POST['cpo_pm_1'], "text")), GetSQLValueString(Money($_POST['cpo_shift_total_1'], "text")), GetSQLValueString(Money($_POST['cpo_am_2'], "text")), GetSQLValueString(Money($_POST['cpo_pm_2'], "text")), GetSQLValueString(Money($_POST['cpo_shift_total_2'], "text")), GetSQLValueString(Money($_POST['cpo_am_3'], "text")), GetSQLValueString(Money($_POST['cpo_pm_3'], "text")), GetSQLValueString(Money($_POST['cpo_shift_total_3'], "text")), GetSQLValueString(Money($_POST['gross_sales_am2'], "text")), GetSQLValueString(Money($_POST['gross_sales_pm2'], "text")), GetSQLValueString(Money($_POST['gross_sales_total2'], "text")), GetSQLValueString(Money($_POST['ticket_am'], "text")), GetSQLValueString(Money($_POST['ticket_pm'], "text")), GetSQLValueString(Money($_POST['ticket_total'], "text")), GetSQLValueString(Money($_POST['over_short_am'], "text")), GetSQLValueString(Money($_POST['over_short_pm'], "text")), GetSQLValueString(Money($_POST['over_short_total'], "text")), GetSQLValueString($_POST['cpo_am_item_1'], "text"), GetSQLValueString($_POST['cpo_am_item_2'], "text"), GetSQLValueString($_POST['cpo_am_item_3'], "text"), GetSQLValueString($_POST['server1_name'], "text"), GetSQLValueString($_POST['server2_name'], "text"), GetSQLValueString($_POST['server3_name'], "text"), GetSQLValueString($_POST['server4_name'], "text"), GetSQLValueString($_POST['server5_name'], "text"), GetSQLValueString($_POST['serv1_cc_am'], "text"), GetSQLValueString($_POST['serv1_cc_pm'], "text"), GetSQLValueString($_POST['serv1_cc_total'], "text"), GetSQLValueString($_POST['serv2_cc_am'], "text"), GetSQLValueString($_POST['serv2_cc_pm'], "text"), GetSQLValueString($_POST['serv2_cc_total'], "text"), GetSQLValueString($_POST['serv3_cc_am'], "text"), GetSQLValueString($_POST['serv3_cc_pm'], "text"), GetSQLValueString($_POST['serv3_cc_total'], "text"), GetSQLValueString($_POST['serv4_cc_am'], "text"), GetSQLValueString($_POST['serv4_cc_pm'], "text"), GetSQLValueString($_POST['serv4_cc_total'], "text"), GetSQLValueString($_POST['serv5_cc_am'], "text"), GetSQLValueString($_POST['serv5_cc_pm'], "text"), GetSQLValueString($_POST['serv5_cc_total'], "text"), GetSQLValueString($_POST['serv_cc_am_total'], "text"), GetSQLValueString($_POST['serv_cc_pm_total'], "text"), GetSQLValueString($_POST['serv_cc_total'], "text"), GetSQLValueString(Money($_POST['mtd_sales'], "text")), GetSQLValueString(Money($_POST['ytd_sales'], "text")), GetSQLValueString(Money($_POST['mtd_tax'], "text")), GetSQLValueString(Money($_POST['ytd_tax'], "text")), GetSQLValueString($_POST['serv_cc_total2'], "text"), GetSQLValueString($_POST['mtd_cc'], "text"), GetSQLValueString(Money($_POST['gca_am'], "text")), GetSQLValueString(Money($_POST['gca_pm'], "text")), GetSQLValueString(Money($_POST['gca_total'], "text")), GetSQLValueString(Money($_POST['adstm'], "text")), GetSQLValueString(date("Y-m-d H:i:s",time()),"text") ); mysql_select_db("restaurant_db", $conn) or die(mysql_error()); $Result1 = mysql_query($insertSQL, $conn) or die(mysql_error()); Other stuff not related to this issue DatePicker link CSS stuff Javascript stuff <body ...> <form method="POST" autocomplete="on" NAME="smartform" action="<?php echo $editFormAction; ?>< onSubmit="clickedButton"> <table ...> <tr> <td height="26" align="center"> <input onBlur="mark(this,'#ffffff','#000000'); this.value=this.value.replace(/,/g,''); this.value=formatCurrency(this.value);" type="text" style="text-align: right" name="currency_am" value="" size="11" id="currency_am" class="fields3" onChange="calcincome(this.form,'currency_am','Currency for Day Shift'); formatCurrency(currency_am);" onFocus="nextfield='coin_am'; mark(this,'#c7dcf9','#688bac')" /> </td> </tr> <tr> <td height="26" align="center"> <input onBlur="mark(this,'#ffffff','#000000'); this.value=this.value.replace(/,/g,''); this.value=formatCurrency(this.value);" type="text" style="text-align: right" name="coin_am" value="" size="11" id="coin_am" class="fields3" onChange="calcincome(this.form,'coin_am','Coin for Day Shift');" onFocus="nextfield='check_am'; mark(this,'#c7dcf9','#688bac')" /> </td> </tr> </table> <input name="new_date" type="hidden" value="<?php echo $row_rsRecordData['new_date']; ?>"> <input TYPE="submit" name="submit" value="Submit" onClick="clickedButton=true"> </p> <p> </p> <input type="hidden" name="MM_insert" value="smartform"> </form>

I thought this forum was here to help people. I know what's in my code and how to protect it. I am not a hacker, so I would not know how to test your code against code injection and in my opinion, this is not important and is not the issue. You seem intent on defending your code, but it doesn't solve my problem, it creates more problems for me to clean up. If you don't have any other ideas on how to help me solve my probem, then please let someone else try.

see the SQL injection problem to understand why this function exists: http://en.wikipedia.org/wiki/SQL_injection

I am not willing to risk code injection by eliminating GetSQLValueString() code. Your code will not work with this code in place. I causes errors for every field on the form and I don't know how to use both without causing errors. I need to find another viable solution.

OK, can you make it work with GetSQLValueString() ? I would like to protect it from code injection.

I don't know what issues you would be referring to. None of the other suggestions on this subject in this forum produced any errors. I am simply trying to enter a value in a php form that populates the MySQL db, and when no value is entered, I would like to have no value returned. I can accomplish this by not using the following code: function Money($float) { return sprintf("%01.2f", $float); } However, I use this code to format two decimal places to my values so I don't end up with 5.1 instead of 5.10 in the db. Can you help me develop code that will return a blank result when no value is given? Your help would be appreciated.

mgallforever, Sorry, I didn't know there was a page two as I am unfamiliar with this forum. Your code produced the following errors upon submission: Warning: Missing argument 2 for GetSQLValueString(), and Notice: Undefined variable: theType ...

After testing: The code below returns (0.00) to all fields with no value. function Money($float) { return sprintf("%01.2f", $float); } The code below returns (0) which is better than 0.00, but doesn't work on negatibe numbers. When negative numbers are introduced, the result is: 0.00 and the negative value is ignored. function Money($float){ return (floatval($float) > 0) ? sprintf("%01.2f", floatval($float)) : 0; } var_dump(Money(0), Money(2.12), Money(3.112)); I am still looking for a solution that will allow for a return value of " " from the db when no value is entered.

function Money($float){ return (floatval($float) > 0) ? sprintf("%01.2f", floatval($float)) : 0; } This doesn't work with negative values.

This works: function Money($float){ return (floatval($float) > 0) ? sprintf("%01.2f", floatval($float)) : 0; } But, Now I get "0" returned in fields with bno value. How do I use the DEFAULT keyword?

Thanks for the explanation! I will look into it.

default value is NULL.

smerny, Still cannot make this function return no value. mgallforever, I don't understand why GetSQLValueString method won't work or why this has anything to do with returning 0.00? I am fairly new to php and don't understand the reasoning behind your code.

If I don't enter anything in the form fields, all of the Money fields are 0.00 since I began using the function.