Writing variables to table?
Moderator: General Moderators
Writing variables to table?
Hi, I'm new to php, and I've been doing online tutorials/manuals to try and understand how to use it. So, I'm just beginning to get a grip on basic php. I've finally figured out how to start using a database and creating tables. I know how to input form data into a table, but I can't seem to figure out how to write a variable from the code into a table, like automatically inserting a date when form is submitted. Maybe I'm going about this the wrong way, and this is probably a dumb question. But as I said, I'm a noob.
So, any help would be much appreciated. Thankyou.
Do you have a specific bit of code or SQL you're having trouble with?
What database are you working with (MySQL)?
Most likely you can use an auto-update TIMESTAMP field or similar to automatically update the time when you update the table, if you're interested in using that. Otherwise, you can easily do it manually in your query.
Ok, a VERY basic PHP / MySQL insert from a form:
(untested, unremarkable example with pseudocode elements)
There are at least dozen ways the above can be handled, using a parameterized query string, using an auto-updated TIMESTAMP type column, creating a UNIX timestamp, etc ad nauseum. Pick a way that makes sense to you and make it work.
What database are you working with (MySQL)?
Most likely you can use an auto-update TIMESTAMP field or similar to automatically update the time when you update the table, if you're interested in using that. Otherwise, you can easily do it manually in your query.
Ok, a VERY basic PHP / MySQL insert from a form:
(untested, unremarkable example with pseudocode elements)
Code: Select all
<?php
if ( FORM WAS POSTED ) {
// connect to MySQL server, select database, all that good stuff here
//....
// process form data
if ( !empty($_POST['somedata']) && ctype_alnum($_POST['somedata']) ) {
// further validation here if you are so inclined
// at least use m_r_e_s() to escape the data properly
$cleanData= mysql_real_escape_string($_POST['somedata']);
// fashion an SQL statement using the now clean data, and NOW() to insert the time
$sql= "INSERT INTO myTable (data_column, timestamp_column) VALUES ( '{$cleanData}', NOW() )";
// execute the query
$result= mysql_query($sql)
OR die( 'Query error:<br />' .$sql. '<br />' .mysql_error() );
// check to make sure a record was inserted
if ( mysql_affected_rows() == 1 ) {
echo '<p>Successfully entered data.</p>';
}
}
} else {
// show form
?>
<form name="testForm" method=POST action="this.php">
<input name="somedata" type="text" />
<input name="submitTestForm" type="submit" />
</form>
<?php } ?>Re: Writing variables to table?
If you have the value in a $variable, all you have to do is put the $variable in the syntax of an INSERT statement, just like the $variables from a form.patrick24 wrote:Hi, I'm new to php, and I've been doing online tutorials/manuals to try and understand how to use it. So, I'm just beginning to get a grip on basic php. I've finally figured out how to start using a database and creating tables. I know how to input form data into a table, but I can't seem to figure out how to write a variable from the code into a table, like automatically inserting a date when form is submitted. Maybe I'm going about this the wrong way, and this is probably a dumb question. But as I said, I'm a noob.So, any help would be much appreciated. Thankyou.
Code: Select all
$sql = "INSERT INTO tablename SET `dt_of_entry` = $dt";Code: Select all
$sql = "INSERT INTO tablename ('last_name','first_name','dt_of_entry') VALUES (' " . $_POST['Lname'] . " ',' " . $_POST['Fname'] . " ','$dt')";feyd | Please use
Then I want to insert the price for the drink into the table automatically, as well as the date:
The drink and credit submits fine into the table, but the price and date columns always come up empty.
Also, I need a way to select the previous balance in the table, so I automatically do the math for the balance after price.
Thanks again.
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Thanks for the help guys, but I should have posted an example of what I'm doing. Here is what I'm using for the form:Code: Select all
<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
Customer: <select name="customer">
<option value="NormA">Arbec, Norm
<option value="SteveB">Barney, Steve
<option value="JamieB">Barney, Jamie
<option value="DarrylB">Berg, Darryl
</select>
Drink: <select name="drink">
<option value="Coffee_12oz">Coffee 12oz.
<option value="Coffee_8oz">Coffee 8oz.
<option value="Tea">Tea
<option value="EmergenC">EmergenC
<option value="Americano">Americano
<option value="Latte">Latte
<option value="Breve">Breve
<option value="Mocha">Mocha
</select>
Credit: <input name="credit" type="text" value="0">
<input type="submit" name="submit">
</form>Code: Select all
if ($drink == Coffee_12oz) {
$price = '1.00';
}
elseif ($drink == Coffee_8oz) {
$price = '0.75';
}
elseif ($drink == Tea) {
$price = '0.50';
}
......
$customer = empty($_POST['customer']) ? die ("ERROR: Select a customer") : mysql_escape_string($_POST['customer']); $drink = empty($_POST['drink']) ? die ("ERROR: Select a drink") : mysql_escape_string($_POST['drink']);
$credit = mysql_escape_string($_POST['credit']);
$date = date(m-d-y);
.......
$query = "INSERT INTO $customer (date, drink, credit, price, balance) VALUES ('$date', '$drink', '$credit', '$price', '$balance')";Also, I need a way to select the previous balance in the table, so I automatically do the math for the balance after price.
Thanks again.
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]1) PHP date() syntax - your call to date() is wrong, so the value is empty.
2) Your if-else statements are performing comparisons against non-existant constants, e.g. Coffee_12oz rather than string literals, e.g. 'Coffee_12oz'. Your $price variable is empty. You must have E_NOTICE errors turned off. If you must do this, I'd recommend using switch() rather than multiple if-else blocks.
3) Your price is a FLOAT / DECIMAL value. What data type is your `price` column? When dealing with FLOAT values, don't surround them with quotes as you would a string type column.
I would strongly suggest refactoring your code / table. You shouldn't be doing all this data manipulation to decide how much the price is, that should be stored in a table someplace, e.g.
All the drink names / pricing is stored in this table and you don't have it hard-coded in your script. If you want to update pricing across all the PHP scripts, you simply update it in one place. Your form pulls down the drinkID / drinkName key / value pairs and when the form is submitted you simply store that drinkID value in your customer data table. Speaking of which.... your customer table should also be refactored into at least two other tables, `customers` and `customerData` with a `customerID` key relating the two. Obviously this is new to you and you won't get it all the first time around.
Some light reading:
MySQL Manual : Tutorial : Using Foreign Keys
Introduction to Database Normalization
2) Your if-else statements are performing comparisons against non-existant constants, e.g. Coffee_12oz rather than string literals, e.g. 'Coffee_12oz'. Your $price variable is empty. You must have E_NOTICE errors turned off. If you must do this, I'd recommend using switch() rather than multiple if-else blocks.
3) Your price is a FLOAT / DECIMAL value. What data type is your `price` column? When dealing with FLOAT values, don't surround them with quotes as you would a string type column.
I would strongly suggest refactoring your code / table. You shouldn't be doing all this data manipulation to decide how much the price is, that should be stored in a table someplace, e.g.
Code: Select all
CREATE TABLE IF NOT EXISTS `drinks` (
drinkID TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
drinkName VARCHAR(25) NOT NULL,
drinkPrice DECIMAL(5,2) NOT NULL,
PRIMARY KEY (drinkID),
UNIQUE (drinkName)
);Some light reading:
MySQL Manual : Tutorial : Using Foreign Keys
Introduction to Database Normalization
Thanks for your help bdlang. Yes, I don't really know what I'm doing, so I'll do some of that reading. Also I think I need to get use to more basic stuff before tackling what I'm trying to do. I just had an idea, and I thought I could pull it off without really knowing what to do, but I guess there's alot more to learn than I thought.
Thanks.
Thanks.
Thanks, bdlang. Maybe a little frustrasted at times but not discouraged.
I've only started learning php since this monday so I think I'm doing good for a noob, I can just get carried away sometimes lol. So for now I'm pulling my self back and learning more basic stuff, but I still haven't given up on my database experiment. I actually got everything working but I still have one more question:
I'm trying to select the last balance entry in the table, but every time it comes up as '3' so the $price is subtracted from 3.

[/img]
Code: Select all
$query = "SELECT balance FROM $customer WHERE id=(SELECT MAX(id))";
$lastbalance = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
$balance = ($lastbalance - $price + $credit);
[/img]
Ok, the first thing you should understand is that $lastbalance isn't a value returned from the database. It's either a MySQL Resource ID value or FALSE. You have to take that and retrieve a value from it using one of the functions (mysql_result(), mysql_fetch_row(), etc). In this case since you're retrieving a single value I'd suggest mysql_result(), e.g.
I'm guessing here this is the calculation you want; don't forget about operator precedence!
I won't second guess your query, but make certain you test that in MySQL prior to trying to manipulate the data returned in PHP.
Code: Select all
$result= mysql_query($query) OR die( 'Error in query:<br />' .$query. '<br />' .mysql_error() );
$lastbalance= mysql_result($result, 0);
$balance= $lastbalance - ( $price + $credit );I won't second guess your query, but make certain you test that in MySQL prior to trying to manipulate the data returned in PHP.
Code: Select all
$result= mysql_query($query) OR die( 'Error in query:<br />' .$query. '<br />' .mysql_error() );
$lastbalance = mysql_result($result, 5);
$balance = $lastbalance - ($price + $credit);Ok, I got that fixed, but now $lastbalance is -1.00, the last value from $result, but it doesn't change after that.
Oh, nevermind. The number after $result refers to the row id, so it's getting the data from the 5th row from the bottom. So, I can select any row number I want, but how do I select the top (one most recently inserted)?
Just a couple of points:
The SQL INSERT statement adds new rows at the end of the table, not to the top. But you should not depend on the "last" (in terms of time) to always be at any particular place in a table. If you need to refer to the "most recent", you need to have a timestamp field in each record, then you can query in descending order of the timestamp and include LIMIT 1 in your query (assuming you're using MySQL, other databases may not support the LIMIT clause, but they usually have an equivalent, such as TOP 5).
I wouldn't use the row parameter of the mysql_request() function, but others may not agree.
The SQL INSERT statement adds new rows at the end of the table, not to the top. But you should not depend on the "last" (in terms of time) to always be at any particular place in a table. If you need to refer to the "most recent", you need to have a timestamp field in each record, then you can query in descending order of the timestamp and include LIMIT 1 in your query (assuming you're using MySQL, other databases may not support the LIMIT clause, but they usually have an equivalent, such as TOP 5).
I wouldn't use the row parameter of the mysql_request() function, but others may not agree.
Ok califdon, I think I get what your saying. This is my code:
I have field id set as my primary key and using auto_increment. So, my result is ORDER BY id DESC so the most recent insert (max id) is at the end (top) of the table. So shoudln't $query = "SELECT balance FROM $customer WHERE id=(SELECT MAX(id))"; work to select the most recent insert query?
I'll try inserting a timestamp field and selecting that for the most recent balance.
Thanks.
Code: Select all
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");
$query = "SELECT balance FROM $customer WHERE id=(SELECT MAX(id))";
$result= mysql_query($query) OR die( 'Error in query:<br />' .$query. '<br />' .mysql_error() );
$lastbalance = mysql_result($result, 0);
$balance = ($lastbalance - $price + $credit);
$query1 = "INSERT INTO $customer (date, drink, credit, price, balance) VALUES ('$date', '$drink', '$credit', '$price', '$balance')";
$query2 = "SELECT * FROM $customer ORDER BY id DESC";
$result1 = mysql_query($query2) or die ("Error in query: $query2. ".mysql_error());
if (mysql_num_rows($result1) > 0) {
echo "<table cellpadding=10 border=1>";
while($row = mysql_fetch_row($result1)) {
echo "<tr>";
echo "<td>".$row[0]."</td>";
echo "<td>".$row[1]."</td>";
echo "<td>".$row[2]."</td>";
echo "<td>".$row[3]."</td>";
echo "<td>".$row[4]."</td>";
echo "<td>".$row[5]."</td>";
echo "</tr>";
}
echo "</table>";
}
else {
echo "No rows found!";
}
mysql_free_result($result);
mysql_close($connection);
}I'll try inserting a timestamp field and selecting that for the most recent balance.
Thanks.