Jump to content


Photo

inserting array into MySQL table


  • Please log in to reply
6 replies to this topic

#1 jansip

jansip
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 01 May 2006 - 11:33 AM

I´m trying to add data from one table to another, where user chooses witch data he wants to add to that table by checking the checkbox. I hope the code is right but I cant figure out the mistake. Please help.
Connect to the database is O.K. and works, but when I process following code and look to the destination table I have a total nonsense in it.

<?
do {
$vybercenik = MySQL_Query("SELECT * FROM cenik");
$pocetcenik = Mysql_num_rows($vybercenik);
if (!$vybercenik):
echo "Došlo k chybě při výběru databáze Ceník.<BR>\n";
break;
endif;
while ($ukazcen = MySQL_Fetch_Array($vybercenik))
echo "
<tr align='right'>
<td><input name='add[$integer]['nazev']' type='hidden' value=".$ukazcen[nazev].">".$ukazcen[nazev]."</td>
<td><input name='add[$integer]['vrobce']' type='hidden' value=".$ukazcen[naz_odru].">".$ukazcen[naz_odru]."</td>
<td><input name='add[$integer]['popis']' type='text' value=''></td>
<td><input name='add[$integer]['cislo']' type='hidden' value=".$ukazcen[jkpov].">".$ukazcen[jkpov]."</td>
<td><input name='add[$integer]['cena']' type='hidden' value=".$ukazcen[pc].">".$ukazcen[pc]."</td>
<td><input name='add[$integer]['sleva']' type='hidden' value=".$ukazcen[koef].">".$ukazcen[koef]."</td>
<td><input name='add[$integer]['dph']' type='hidden' value=".$ukazcen[dan].">".$ukazcen[dan]."</td>
<td><input name='add[$integer]['mj']' type='hidden' value=".$ukazcen[mj].">".$ukazcen[mj]."</td>
<td><input name='vybrano' type='checkbox'></td>
";} while (false);
?>
<tr align="right"><td colspan=4 align="left"><a href="vybertxt.php?vracim=zpet">Zpět na výběr souboru</a></td>
<td colspan=7><input type=submit value="Vlož produkty &gt;&gt;"></td></tr>
</form>
</table>


<?
if ($_GET['pridat'] == "ano") {
$save = array();
foreach ($add as $radek)
{
$save[] = "('{$radek['nazev']}', '{$radek['vyrobce']}', '{$radek['popis']}', '{$radek['cislo']}', '{$radek['cena']}', '{$radek['sleva']}', '{$radek['dph']}', '{$radek['mj']}')";
}
$sql = "INSERT INTO vyrobky (vyr_nazev, vyr_vyrobce, vyr_popis, vyr_cislo, vyr_cena, vyr_sleva, vyr_dph, vyr_mj) VALUES ".implode(', ', $save);
$do = MySQL_query($sql) or die(mysql_error());
}
?>

#2 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 01 May 2006 - 01:55 PM

You're going to have to give us more information, and please use code tags when you post code. Are you getting any errors? Is any of the data showing up in the second table correctly?

And I can only assume that the PHP code is on a different page after they submit it, correct?

Info: PHP Manual


#3 jansip

jansip
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 02 May 2006 - 07:46 AM

[!--quoteo(post=370279:date=May 1 2006, 03:55 PM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ May 1 2006, 03:55 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
You're going to have to give us more information, and please use code tags when you post code. Are you getting any errors? Is any of the data showing up in the second table correctly?

And I can only assume that the PHP code is on a different page after they submit it, correct?
[/quote]

O.K. I describe the whole situation. I have first table called "cenik" from which I take data to be shown on page where user select with checkbox which data he wants to put to other table called "vyrobky".
When I look on array with
echo "<pre>"; print_r($add); exit;
It shows me the array is O.K. and data are correct, but when i continue with this
<?
if ($_GET['pridat'] == "ano") {
$save = array();
foreach ($add as $radek)
{
$save[] = "('{$radek['nazev']}', '{$radek['vyrobce']}', '{$radek['popis']}', '{$radek['cislo']}', '{$radek['cena']}', '{$radek['sleva']}', '{$radek['dph']}', '{$radek['mj']}')";
}
$sql = "INSERT INTO vyrobky (vyr_nazev, vyr_vyrobce, vyr_popis, vyr_cislo, vyr_cena, vyr_sleva, vyr_dph, vyr_mj) VALUES ".implode(', ', $save);
$do = MySQL_query($sql) or die(mysql_error());
}
?>

and then look into table I have total nonsense in target table "vyrobky". The nonsense is like when character field there is only one letter, and when numeric filed only one number. These do NOT have anything with the data passed by form. Im not sure what I am doing bad. My only idea is, that the field arent counted right. But I dont know how to make that works. Thanks for replies

P.S. I dont get any errors, the code just make itself as everything is OK
I also forgot to paste the start of the page. It aims itself.
<table border="1" class="list" bgcolor="silver">
<form action="vyberprod.php?pridat=ano" method="post">


#4 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 02 May 2006 - 01:31 PM

First it looks like you're assuming register_globals is enabled. You should change your coding style to always use the superglobal array whether or not register_globals is enabled. This way your code will work when you move it to a new server no matter what the setting.

Here's some code that should work, if I'm understanding your input:
<?php
if ($_GET['pridat'] == "ano") {
    foreach ($_POST['add'] as $num => $radek) {
        $qtmp = array();
        foreach ($radek as $key => $val)
             $qtmp[] = 'vyr_' . $key " = '" . mysql_real_escape_string($val) . "'";
        $sql = "insert into vyrobky set " . implode(', ', $qtmp);
        $rs = mysql_query($q) or die('Problem with query: ' . $sql . '<br>' . mysql_error());
    }
}
?>
I use the alternate form of the mysql insert command and use the mysql_real_escape_string() function to make sure anything that might cause problems in the data is escaped.

I create a new query for each row that's passed back.

Ken


#5 jansip

jansip
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 02 May 2006 - 03:39 PM

[!--quoteo(post=370540:date=May 2 2006, 03:31 PM:name=kenrbnsn)--][div class=\'quotetop\']QUOTE(kenrbnsn @ May 2 2006, 03:31 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
First it looks like you're assuming register_globals is enabled. You should change your coding style to always use the superglobal array whether or not register_globals is enabled. This way your code will work when you move it to a new server no matter what the setting.

Here's some code that should work, if I'm understanding your input:
<?php
if ($_GET['pridat'] == "ano") {
    foreach ($_POST['add'] as $num => $radek) {
        $qtmp = array();
        foreach ($radek as $key => $val)
             $qtmp[] = 'vyr_' . $key " = '" . mysql_real_escape_string($val) . "'";
        $sql = "insert into vyrobky set " . implode(', ', $qtmp);
        $rs = mysql_query($q) or die('Problem with query: ' . $sql . '<br>' . mysql_error());
    }
}
?>
I use the alternate form of the mysql insert command and use the mysql_real_escape_string() function to make sure anything that might cause problems in the data is escaped.

I create a new query for each row that's passed back.

Ken
[/quote]

Thanks a lot for quick answer.

I tried your code, but I get this answer:

Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING in C:\www\pokus\vyberprod.php on line 59

When finding the line, its the line with mysql_real_escape_string($val).
I dont have a clue how to figure this out. Any idea??

I also forgot to mention, that in destination table I have one column thats set to auto_increment. It isnt mentioned in code because I dont insert any information in it. But do I have to count with this in your code, or not? From what I uderstood from insert function manual I should specify the columns I am targeting in the order i get information from the form, am I right?

Jan

#6 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 02 May 2006 - 04:10 PM

I forgot one concatenation operator, ".", in the line. Here is the corrected line:
<?php $qtmp[] = 'vyr_' . $key . " = '" . mysql_real_escape_string($val) . "'"; ?>

As for your other questions.
[ol type=\'1\'][*]If you don't mention the auto-increment field in your insert command then mysql will "do the right thing" and store the auto incremented value. If you want to exclude the column from the generated query, change the seconed foreach() to be:
<?php
        foreach ($radek as $key => $val)
             if ($key != 'put the name of the autoincrement field here')
                     $qtmp[] = 'vyr_' . $key " = '" . mysql_real_escape_string($val) . "'";
?>
Replace "put the name of the autoincrement field here" with the real name in your table.[*]When you use the alternative insert syntax, the order doesn't matter and you can leave out fields. Any fields not mentioned will get their default values.[/ol]
Ken

#7 jansip

jansip
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 02 May 2006 - 04:58 PM

[!--quoteo(post=370601:date=May 2 2006, 06:10 PM:name=kenrbnsn)--][div class=\'quotetop\']QUOTE(kenrbnsn @ May 2 2006, 06:10 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I forgot one concatenation operator, ".", in the line. Here is the corrected line:
<?php $qtmp[] = 'vyr_' . $key . " = '" . mysql_real_escape_string($val) . "'"; ?>

As for your other questions.
[ol type=\'1\']
[*]If you don't mention the auto-increment field in your insert command then mysql will "do the right thing" and store the auto incremented value. If you want to exclude the column from the generated query, change the seconed foreach() to be:
<?php
        foreach ($radek as $key => $val)
             if ($key != 'put the name of the autoincrement field here')
                     $qtmp[] = 'vyr_' . $key " = '" . mysql_real_escape_string($val) . "'";
?>
Replace "put the name of the autoincrement field here" with the real name in your table.
[*]When you use the alternative insert syntax, the order doesn't matter and you can leave out fields. Any fields not mentioned will get their default values.
[/ol]
Ken
[/quote]

You are quite fast thanks for that.
I managed to correct the code, but I got new error messages.

Warning: Invalid argument supplied for foreach() in C:\www\pokus\vyberprod.php on line 56

Warning: Invalid argument supplied for foreach() in C:\www\pokus\vyberprod.php on line 58

For better helping me with mistake I paste the whole page "AS IT IS" below.

<?
Header("Expiries: ".GMDate("D, d M Y H:i:s")." GMT");
require "sql.php";
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<meta http-equiv="Content-Type" content="text/html"; charset="windows-1250">

<html>
<head>
    <title>Výběr produktů pro zařazení</title>
<link rel="STYLESHEET" type="text/css" href="css/styl.css">
</head>

<body>
<h2>Vyberte produkty k zařazení:</h2>
<p>
V následující sekci vyberte produkty k přidání do E-semexu.
 Ve sloupci <b>Přidat?</b> zaškrtněte zda produkt přidat či nikoliv.<br>
</p>

<table border="1" class="list" bgcolor="silver">
<form action="vyberprod.php?pridat=ano" method="post">
<tr><td colspan=11><b>Seznam převáděných produktů</b></td></tr>
<tr class="podnadpis"><td>Název výrobku</td><td>Výrobce</td><td>Popis</td><td>JKPOV (Číslo výrobku)</td><td>Prodejní cena</td><td>Sleva:</td><td>DPH</td><td>MJ</td><td>Přidat?</td></tr>
<?
do {
    $vybercenik = MySQL_Query("SELECT * FROM cenik");
    $pocetcenik = Mysql_num_rows($vybercenik);
    if (!$vybercenik):
        echo "Došlo k chybě při výběru databáze Ceník.<BR>\n";
        break;
    endif;
    while ($ukazcen = MySQL_Fetch_Array($vybercenik))
    echo "
<tr align='right'>
<td><input name='add_nazev' type='hidden' value=".$ukazcen[nazev].">".$ukazcen[nazev]."</td>
<td><input name='add_vyrobce' type='hidden' value=".$ukazcen[naz_odru].">".$ukazcen[naz_odru]."</td>
<td><input name='add_popis' type='text' value=''></td>
<td><input name='add_cislo' type='hidden' value=".$ukazcen[jkpov].">".$ukazcen[jkpov]."</td>
<td><input name='add_cena' type='hidden' value=".$ukazcen[pc].">".$ukazcen[pc]."</td>
<td><input name='add_sleva' type='hidden' value=".$ukazcen[koef].">".$ukazcen[koef]."</td>
<td><input name='add_dph' type='hidden' value=".$ukazcen[dan].">".$ukazcen[dan]."</td>
<td><input name='add_mj' type='hidden' value=".$ukazcen[mj].">".$ukazcen[mj]."</td>
<td><input name='vybrano' type='checkbox'></td>
";} while (false);
?>
<tr align="right"><td colspan=4 align="left"><a href="vybertxt.php?vracim=zpet">Zpět na výběr souboru</a></td>
<td colspan=7><input type=submit value="Vlož produkty &gt;&gt;"></td></tr>
</form>
</table>
Dále ve sloupci <b>Kategorie?</b> vyberte kategorii do které chcete produkt zařadit.<br>
Zde se vychází z předpokladu že kategorie máte založené a logicky se Vám tedy nabídnou ty co jsou momentálně dostupné.
<?php
if ($_GET['pridat'] == "ano") {
    foreach ($_POST['add'] as $num => $radek) 
        $qtmp = array();
        foreach ($radek as $key => $val){
             $qtmp[] = 'vyr_' . $key . " = '" . mysql_real_escape_string($val) . "'";
        $sql = "insert into vyrobky set " . implode(', ', $qtmp);
        $rs = mysql_query($sql) or die('Problem with query: ' . $sql . '<br>' . mysql_error());
    }
}

?>
</body>
</html>





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users