Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PHP syntax regarding INSERT INTO

Posted on 2015-01-23
8
Medium Priority
?
110 Views
Last Modified: 2015-01-29
I am trying to write a statement that inserts 3 fields of data into multiple records. I believe my logic is correct but am having problems with the syntax. I've tried various combinations of "" and '' but still no success.

My statement is:

WHILE ($row = mysql_fetch_row($results))
{
"INSERT INTO tbl_cnpa_ic_comments
(eastern,file,comment) VALUES ('$eastern','$_POST[f".$row[1]."]','$_POST[c".$row[1]."]');"
}

Might someone offer a solution? Thanks.
0
Comment
Question by:lepirtle
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 40567548
First, that string is not a query without the 'mysql_query' function.  It is just a string.  Also, I avoid compound statements like that.  I would separate out the $_POST variables into discrete variables like this and then use them in the SQL statement.  You may also have to create a second connection for the INSERT because you haven't finished with the first connection that you are getting your results from.
WHILE ($row = mysql_fetch_row($results))
{
$ffile = $_POST[f".$row[1]."];
$ccomnt = $_POST[c".$row[1]."];
$rez1 = mysql_query("INSERT INTO tbl_cnpa_ic_comments (eastern, file, comment) VALUES ('$eastern', '$ffile', '$ccomnt')");
}

Open in new window

0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40567552
what are you trying to do?
assuming that $row[1] contains the value '123'
what i understood is that you're inserting the post variable coming from the fields f123 and c123?

if so: here's the final sql

$mysqli->query("insert into tbl_cnpa_ic_comments (eastern,file,comment) VALUES ('" . $eastern . "', '" . $_POST["f".$row[1]] . "', '" . $_POST["c".$row[1]] . "')");

give us more details on what you're trying to do!
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40567634
I see a couple of issues here.  

First and foremost you must get off MySQL now and never go back to it again.  PHP is doing away with MySQL support.  http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

After that, you want to understand the meaning of quotes in PHP.  It's a syntax thing, applicable to all PHP code.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12241-Quotation-Marks-in-PHP.html

Then to follow on, you would never use an external variable from $_POST in a query string.  You would want to escape or prepare the variables for use in the query string.   Some of the common design patterns are shown in the article above.

If you can use PHP var_dump($_POST) and show us the output we can probably help with the basic statements you need.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lepirtle
ID: 40568386
Thank you all. Sorry for the delay in responding but I took the time to compose a reply that would cover all your suggestions.

Hi Dave Baldwin,
Thank you for your suggestion but I tried your code but received the following message:
Parse error: syntax error, unexpected '"', expecting ']' on line 29

Hi Dany Balian:
I tried your suggestion, though I changed the
$mysqli->query("insert into
to
$mysql_query("insert into

and received an error message:
Parse error: syntax error, unexpected '}' in line 29

To answer your question of what I am trying to do (and I will shorten the table names to simply tbl_files and tbl_comments to make things simpler):
I have a form that captures 2 values, f and c, and it captures multiple records of each of those 2 values, depending on the number of rows in tbl_files.
The applicable code for this form is:
<?php

$sql = "SELECT *
        FROM tbl_files
        ORDER BY file";
             
$results = mysql_query($sql);

$num_rows =mysql_num_rows($results);
?>

<form>
<?php
WHILE ($row = mysql_fetch_row($results))
{
echo
"
<img src=\"".$row[0].$row[1]."\"/>
<textarea name='f".$row[1]."'id='f".$row[1]."' style='display:none;'>".$row[1]."</textarea></td>
   </tr>
   <tr>
      <td><textarea name='c".$row[1]."'id='c".$row[1]."; placeholder='Add your comments'></textarea>
      </td>
   </tr>
</table>";
}
?>
</table>
<input name="submit1" type="submit" id="submit1" value="Submit" />
</form>

What I want is to insert those 2 values (along with a time/date stamp) as multiple records into a table named tbl_comments. But what is causing me problems (which is the reason for the question I posted to Experts Exchange) is that the number of records that will be inserted depends on the number of records contained in the tbl_files. That is why I thought that the INSERT PHP could read something like:

<?php
/* Create a date parameter to insert into the transaction date field.*/
$eastern = date ("Y-m-d H:i:s");

$sql = "SELECT *
        FROM tbl_files";
             
$results = mysql_query($sql); or die(mysql_error());

WHILE ($row = mysql_fetch_row($results))
{
"INSERT INTO tbl_comments
(eastern,file,comment) VALUES ('$eastern','$_POST[f".$row[1]."]','$_POST[c".$row[1]."]');"
}

Obviously I can hard-code the VALUES to be inserted but I think there is a better way which is what you have attempted to do.

Hello Ray Paseur,
With much dismay, I read your warning about not using MySQL. I am so sorry to read that as thousands of others probably are. I have read many of your postings and you have directly helped me a number of times so as you can probably see from my questions I am a very novice “script kiddy” to use a phrase from your article. I surely respect your expertise but I am not operating on the same level as you. I simply volunteer for very basic web projects for neighborhood not-for-profits. Nothing involving money but things as simple as the above project or something like allowing members of a running club to track their member’s daily exercise. A few years ago Godaddy decided to stop support of ColdFusion a scripting language that I thought would be around for a while since it was a Macromedia/Adobe product. But that stoppage caused me to learn, what I thought was future-proof, MySQL/ PHP. It was a steep learning curve for this old man but I managed to understand some of the basics with hundreds of frustrating hours and a fair amount of  help from Experts Exchange. So I will take your advice and begin gathering data for MySQLi or PDO but it will take me quite a while just to be able to begin their use let alone begin to write something workable. And I say this apologetically because I don’t have the knowledge that is required to make the transition so quickly, for the current project for the  not-for-profit I am helping – a project that will be used another 6 times this year for only a few days at a time - I just need to complete it then begin the migration and learning curve to understand one of my two new options for my future volunteering.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 501 total points
ID: 40568407
Can we come back to this, please...  Never use an external variable from $_POST in a query string.  It's a sure way to get your data base destroyed, just as soon as some hacker finds your scripts.  If you can use PHP var_dump($_POST) and show us the output we can probably help with the statements you need.
0
 
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 498 total points
ID: 40568484
This...

{
"INSERT INTO tbl_comments
(eastern,file,comment) VALUES ('$eastern','$_POST[f".$row[1]."]','$_POST[c".$row[1]."]');"
}

doesn't do anything at all.  It doesn't assign it to a string or make a query.
0
 
LVL 34

Assisted Solution

by:Slick812
Slick812 earned 501 total points
ID: 40568539
greetings lepirtle, , You have some helpful advice from experts already, , But in your database $row[ ] page output for the <textarea> you use a row value for the name as -
    name='c".$row[1]."'

This makes me wonder about the programming "organization" of your many many <textarea> inputs, What I almost always do is use a Numeric ordered naming, such as
<textarea name='c1'>
<textarea name='c2'>
<textarea name='c3'>
BUT in html forms you can have an array format for name, so if you have more than 3 text areas you might try
<textarea name='c[]'>

then you can just retrieve the PHP $_POST[ ] for the c text areas numerically OR as an array

You really do need to escape ALL user input for a SQL string, but your question is not about that, as far as what you present, a workable SQL string build might be -
$SQL ="INSERT INTO tbl_comments
(eastern,file,comment) VALUES ('$eastern','".$_POST["f".$row[1]]."','".$_POST["c".$row[1]]."')";

you just copied the Textarea output as f".$row[1]." for the SQL, but that will not work, as you know.
0
 

Author Closing Comment

by:lepirtle
ID: 40578431
Thanks to all of you. I simply ran out of time to complete my project in the way that I wanted (by using your suggestions) so resorted to hard-coding a couple pages. Though I did not utilize all the information you volunteered I have even more knowledge to continue to improve my code and I thank you for providing that, as well as the related subject material.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to create an extensible mechanism for linked drop downs.
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question