Need help with query

[sgtpepper]

Hi,

I am using a form to update pictures in a database.

I have the following query
$query= "REPLACE INTO featuredlistings (Idf,picture_name,picture2_name,picture3_name,picture4_name)
VALUES ('$Idf','$picture_name','$picture2_name','$picture3_name','$picture4_name')";

I would like to be able to use this script to update the database as well.

For example:

Someone wants to place brand new data into the table....they fill out this form...and boom...data added.

If they want to edit the data for picture2, they go to this form...fill in the data for picture 2 and ONLY picture 2's field gets updated.

Now, if they enter data ONLY for picture 2 it also deletes the other data in the other picture fields.

How can I fix this?

[tekky]

make your query dynamic by having a loop go through the submitted variables
for $POST

while(list($key, $val) = each($_POST))
{
     if ($val) //if a value exits in the post variable, add it to the list to replace
     {
          $fields .= "'$key', "; //building field list
          $values .= "'$val', "; //building values list
     }
}
$sql = "REPLACE INTO $table (" .substr($fields,0,strlen($fields)-1). ") VALUES (" .substr($values,strelen($values)-1). ")"; //making them all into one big happy SQL Query

[sgtpepper]

Sorry for asking this...but can you show me how to implement this in the following code? I tried but had some problems...still learning this...

<?
$username="xxx";
$password="xxx";
$database="xxx";

mysql_pconnect("xxx",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

// Below will display listings for specific ID///
$query= "REPLACE INTO featuredlistings (Idf,picture_name,picture2_name,picture3_name,picture4_name,office_id) VALUES
('$Idf','$picture_name','$picture2_name','$picture3_name','$picture4_name','$office_id')";

$result=mysql_query($query) or die (mysql_error());
exec("mkdir /var/www/html/propimages/featured/$Idf");
exec("cp $picture /var/www/html/propimages/featured/$Idf/$picture_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture2_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture3_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture4_name");
?>

[tekky]

where do you get your $pic* variables from

[sgtpepper]

It gets sent from the form.

IE:

<td width="167"><div align="right">Picture 1</div></td>
          <td width="534"> <div align="left">
              <input type="File" name="picture" size="25">
            </div></td>
        </tr>
        <tr>
          <td width="167"><div align="right">Picture 2</div></td>
          <td width="534"> <div align="left">
              <input type="File" name="picture2" size="25">
            </div></td>

[tekky]

<?
$username="xxx";
$password="xxx";
$database="xxx";
while(list($key, $val) = each($_POST)) 
{ 
     $$key = $val; //make $_POST vars into named vars like globals ie $_POST['Idf'] into $Idf equal to $_POST['Idf']
     if ($val) //if a value exits in the post variable, add it to the list to replace 
     { 
          $fields .= "'$key', "; //building field list 
          $values .= "'$val', "; //building values list 
     } 
} 
$query = "REPLACE INTO featuredlistings (" .substr($fields,0,strlen($fields)-1). ") VALUES (" .substr($values,strlen($values)-1). ")"; //making them all into one big happy SQL Query

mysql_pconnect("xxx",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

// Below will display listings for specific ID///

$result=mysql_query($query) or die (mysql_error());
exec("mkdir /var/www/html/propimages/featured/$Idf");
exec("cp $picture /var/www/html/propimages/featured/$Idf/$picture_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture2_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture3_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture4_name");
?>

from what I have of your code... that should work....

[sgtpepper]

I get

Call to undefined function: strelen()

[stolzyboy]

Originally posted by sgtpepper
I get

Call to undefined function: strelen()

it was a typo by him, change it to strlen

[sgtpepper]

Ok...now

You have an error in your SQL syntax near ''Idf', 'office_id', 'submit',) VALUES ( )' at line 1

[tekky]

appears the substr isnt clipping the last , off the $fields var...

echo "fields=$fields<br>values=$values<br>";

and see what you get...
may have to break the $query assignment into smaller lines to make sure I Didnt jack that up....

[sgtpepper]

I get

fields='Idf', 'office_id', 'submit',
values='897966', '1', 'Add Featured Listing',
You have an error in your SQL syntax near ''Idf', 'office_id', 'submit',) VALUES ( )' at line

[tekky]

<?
$username="xxx";
$password="xxx";
$database="xxx";
while(list($key, $val) = each($_POST)) 
{ 
     $$key = $val; //make $_POST vars into named vars like globals ie $_POST['Idf'] into $Idf equal to $_POST['Idf']
     if ($val) //if a value exits in the post variable, add it to the list to replace 
     { 
          $fields .= "'$key', "; //building field list 
          $values .= "'$val', "; //building values list 
     } 
} 
$query = "REPLACE INTO featuredlistings (" .substr($fields,0,strlen($fields)-2). ") VALUES (" .substr($values,strlen($values)-2). ")"; //making them all into one big happy SQL Query

mysql_pconnect("xxx",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

// Below will display listings for specific ID///

$result=mysql_query($query) or die (mysql_error());
exec("mkdir /var/www/html/propimages/featured/$Idf");
exec("cp $picture /var/www/html/propimages/featured/$Idf/$picture_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture2_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture3_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture4_name");
?>

Fixed... typo and all, forgot to take into account the space after the commas, just had to change the strlen()-1 to strlen()-2

[sgtpepper]

um...close...

You have an error in your SQL syntax near ''Idf', 'office_id', 'submit') VALUES (, )' at line 1

[tekky]

oh damn major mistake in substr of $values section

<?
$username="xxx";
$password="xxx";
$database="xxx";
while(list($key, $val) = each($_POST)) 
{ 
     $$key = $val; //make $_POST vars into named vars like globals ie $_POST['Idf'] into $Idf equal to $_POST['Idf']
     if ($val) //if a value exits in the post variable, add it to the list to replace 
     { 
          $fields .= "'$key', "; //building field list 
          $values .= "'$val', "; //building values list 
     } 
} 
$query = "REPLACE INTO featuredlistings (" .substr($fields,0,strlen($fields)-2). ") VALUES (" .substr($values,0,strlen($values)-2). ")"; //making them all into one big happy SQL Query

mysql_pconnect("xxx",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

// Below will display listings for specific ID///

$result=mysql_query($query) or die (mysql_error());
exec("mkdir /var/www/html/propimages/featured/$Idf");
exec("cp $picture /var/www/html/propimages/featured/$Idf/$picture_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture2_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture3_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture4_name");
?>

This is fixed... hopefully now 😃
the mistake was

substr($values,strlen($values)-2).
//instead of
substr($values,0,strlen($values)-2).

[sgtpepper]

Hmmm....the error I am getting now is

You have an error in your SQL syntax near ''Idf', 'office_id', 'comments', 'submit') VALUES ('897966', '1', 'fdsfds', 'Add ' at line 1

The php script is:

<?
$username="xxx";
$password="xxx";
$database="xxx";
while(list($key, $val) = each($_POST))
{
     $$key = $val; //make $_POST vars into named vars like globals ie $_POST['Idf']
into $Idf equal to $_POST['Idf']
     if ($val) //if a value exits in the post variable, add it to the list to replace
     {
          $fields .= "'$key', "; //building field list
          $values .= "'$val', "; //building values list
     }
}
$query = "REPLACE INTO featuredlistings (" .substr($fields,0,strlen($fields)-2). ")
VALUES (" .substr($values,0,strlen($values)-2). ")"; //making them all into one big
happy SQL Query

mysql_pconnect("xxx",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$result=mysql_query($query) or die (mysql_error());
exec("mkdir /var/www/html/propimages/featured/$Idf");
exec("cp $picture /var/www/html/propimages/featured/$Idf/$picture_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture2_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture3_name");
exec("cp $picture2 /var/www/html/propimages/featured/$Idf/$picture4_name");
?>

The form code is:

<form enctype="multipart/form-data" action="addfeaturedscript.php" method="POST">
<?php 
include("adminheader.php");
?>


  <div align="center"> 
    <center>
      <table border="0" cellspacing="0" cellpadding="0" width="701">
        <tr> 
          <td colspan="2"> <hr color="#F2891D"> </td>
        </tr>
        <tr> 
          <td colspan="2"><p align="center">To add/edit a featured listing to 
              your website, simply use the form below. </p>
            <p align="center"><strong>You must enter the correct MLS number in 
              order for your additional images to appear.</strong></p></td>
        </tr>
        <tr> 
          <td width="167"> <div align="right">MLS Id:</div></td>
          <td width="534"> <div align="left"> 
              <input type="Text" name="Idf" size="15">
              <input type="hidden" name="office_id" value="1">
            </div></td>
        </tr>
        <tr> 
          <td width="167"><div align="right"></div></td>
          <td width="534"><div align="left"></div></td>
        </tr>
        <tr> 
          <td width="167"><div align="right">Picture 1</div></td>
          <td width="534"> <div align="left"> 
              <input type="File" name="picture" size="25">
            </div></td>
        </tr>
        <tr> 
          <td width="167"><div align="right">Picture 2</div></td>
          <td width="534"> <div align="left"> 
              <input type="File" name="picture2" size="25">
            </div></td>
        </tr>
        <tr> 
          <td width="167"><div align="right">Picture 3</div></td>
          <td width="534"> <div align="left"> 
              <input type="File" name="picture3" size="25">
            </div></td>
        </tr>
        <tr> 
          <td width="167"><div align="right">Picture 4</div></td>
          <td width="534"> <div align="left"> 
              <input type="File" name="picture4" size="25">
            </div></td>
        </tr>
        <tr> 
          <td width="167"><div align="right">Picture 5</div></td>
          <td width="534"> <div align="left"> 
              <input type="File" name="picture5" size="25">
            </div></td>
        </tr>
        <tr> 
          <td width="167"><div align="right">Picture 6</div></td>
          <td width="534"> <div align="left"> 
              <input type="File" name="picture6" size="25">
            </div></td>
        </tr>
        <tr> 
          <td><div align="right"></div></td>
          <td>&nbsp;</td>
        </tr>
        <tr> 
          <td><div align="right">Additional Comments</div></td>
          <td><textarea name="comments" cols="50" rows="10" id="comments"></textarea></td>
        </tr>
        <tr> 
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
        <tr> 
          <td colspan="2"> <p align="center"> 
              <input type="submit" name="submit" value="Add Featured Listing">

      </td>
    </tr>
  </table>
</center>
  </div>
</form>

</p>

<?php
include("adminfooter.php");
?>

What could I be doing wrong?

[tekky]

try this little change...

<?
$username="xxx";
$password="xxx";
$database="xxx";
while(list($key, $val) = each($_POST))
{
     $$key = $val; //make $_POST vars into named vars like globals ie $_POST['Idf'] into $Idf equal to $_POST['Idf']
     if ($val) //if a value exits in the post variable, add it to the list to replace
     {
          $fields .= "'$key', "; //building field list
          if ($key == 'Idf' || $key == 'office_id') { $values .= "$val, "; } //leave off the 's if its a numerical value... from my experience this always errors...
          else { $values .= "'$val', "; } //building values list
     }
}
$query = "REPLACE INTO featuredlistings (" .substr($fields,0,strlen($fields)-2). ")
VALUES (" .substr($values,0,strlen($values)-2). ")"; //making them all into one big
happy SQL Query

[sgtpepper]

Now
You have an error in your SQL syntax near ''Idf', 'office_id', 'submit') VALUES (897966, 1, 'Add Featured Listing')' at line 1

[stolzyboy]

ya know you should probably troubleshoot on your own a little

do this after you set your query with $query = "";

echo $query;

that will echo out your query, and you can then tell if there are fieldnames missing, values missing, quotes in wrong spots, etc...

[sgtpepper]

It seems to be adding my 'submit' button the the query.

REPLACE INTO featuredlistings ('Idf', 'office_id', 'submit') VALUES (897966, 1, 'Add Featured Listing')You have an error in your SQL syntax near ''Idf', 'office_id', 'submit') VALUES (897966, 1, 'Add Featured Listing')' at line 1

Why would it be showing 'submit' in the query....I assume this is were the problem is as my table doesnot have a submit field.

[tekky]

ahhh duh, yah, it adds it because $_POST['submit'] gets set when you click the button

make the if ($val) check read

if ($key != 'submit' && $val)