Solved

PHP syntax regarding INSERT INTO

Posted on 2015-01-23
8
97 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 82

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 108

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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 108

Accepted Solution

by:
Ray Paseur earned 167 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 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 166 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 33

Assisted Solution

by:Slick812
Slick812 earned 167 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now