Solved

PHP syntax regarding INSERT INTO

Posted on 2015-01-23
8
102 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 83

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 109

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 109

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 83

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
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…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

773 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