calculation in mysql query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

calculation in mysql query

Post by infomamun »

Hi there,
I want to insert value from calculation in mysql query. How to do that?
For example:

Code: Select all

$table = $Post['table'];
$array = (100,200,300,400,500);
$value = explode(',',$array);
$limit = count($value);
$counter = 1;
//........Connection string
while($counter<=$limit){
mysql_query("INSERT INTO $table (amount1, amount2) Values($value[$counter],$value[$counter+1])")
$counter=$counter+1;
}
In the above example table name in mysql query will be derived from a post value of a html form. Value of respective column of mysql table will be derived from explode value.
My question is how to write proper sql query string so that variable name (like $table) and calculation ($value[$counter+1]) will be executed in the sql query?
(like "INSERT INTO".$table."(amount1,amount2).....")

Any help will be appreciated.

Thanks in advance
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: calculation in mysql query

Post by Christopher »

First, these two lines are strange. The explode() function splits a string into an array on a specified character. But you are trying to explode an array?!?

Code: Select all

$array = (100,200,300,400,500);
$value = explode(',',$array);
Just guessing what you want, but try:

Code: Select all

$table = $_POST['table'];     // This is VERY dangerous! Validate the value if you must do this!
$value = array(100,200,300,400,500);
$limit = count($value);
$counter = 1;
//........Connection string
while($counter<=$limit){
     mysql_query("INSERT INTO $table (amount1, amount2) VALUES (" . $value[$counter] . ", " . $value[++$counter] . ")")
}
(#10850)
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

Re: calculation in mysql query

Post by infomamun »

I am not exploding an array. The array variable was used to show that I am breaking values into an array. It was just a variable, nothing else. Actually, I was able to figure out the correct mysql_query myself after several tries, after posting the problem here. I have used the following string:

Code: Select all

mysql_query("INSERT INTO $table (amount1, amount2) Values('". $value[$counter] . "', '". $value[$counter+1].'")")
Please notice here that I have used single and double quotation marks combinedly and it is working fine.
Please tell me whether my string is a wrong way (although it executes correctly) or not?

Another problem I am facing that I have to store time in mysql database. So, I have added a column which is in TIME format. TIME format means hh:mm:ss , but I want to store only hh:mm not the second. But while I am trying to store hh:mm from php, it is automatically adding :00 as second after hh:mm.
How can I store time in hh:mm format in a mysql database and what would be the format of that time column in the database?
User avatar
jayson.ph
Forum Contributor
Posts: 165
Joined: Mon Jan 02, 2012 9:20 am
Location: MP
Contact:

Re: calculation in mysql query

Post by jayson.ph »

You mean?

Code: Select all

$counter_ = 1;
$sql = "select->table->field->value";
$result = mysql_query($sql);
$loop
$counter++;
Hours->

Code: Select all

echo date("g:i a");
Post Reply