inserting arrays into db

ppgpilot

I am trying to call data from a db and then input the data into another db
the data is for a diet website
the user chooses a recipe.
the recipe has several rows with several fields i.e.

item1, calorie, fat, protein
item2, calorie, fat, protein
item3, calorie, fat, protein
etc.

I need to pull the entries from the “recipe” db and place them into the “history” db containing the same field names.

I know I need a loop, but have not found the proper way to solve the problem

My latest attempt is included below.

I can echo the data correctly, but only the last entry enters the db

Any assist would be appreciated.

$sql=("select * from $tbl5 where recipe_name = '$recipe_name' ");
$result = mysql_query($sql);
$works = mysql_num_rows($result);
require 'my_error.php';

while($rows = mysql_fetch_array($result))
{

    $item = $rows['item'];
    $amount = $rows['amt'];
    $calorie = $rows['calorie'];
    $fat = $rows['fat'];
    $fat_calories = $rows['fat_calories'];
    $carb = $rows['carb'];
    $protein = $rows['protein'];
    $sugar = $rows['sugar'];


// insert this data into the history ref the date entered on the initial page
$sql = ("insert into $tbl3

    (
    user_name,
    entry,
    day,
    item,
    amt,
    calorie,
    fat,
    fat_calories,
    carb,
    protein,
    sugar

    )
    values
    (

    '$user_name',
    '$entry',
    '$day',
    '$item',
    '$amount',
    '$calorie',
    '$fat',
    '$fat_calories',
    '$carb',
    '$protein',
    '$sugar'
    )

    ");








// end while
}

$result = mysql_query($sql);

tomhath

Move $result = mysql_query($sql); up into the WHILE loop.

mariaak83

Hi!

The problem in your script is that you create a query for each one of the rows you get from the recipe db but you execute only one query because the mysql_query line is out of the loop (after while).

Regards!

ppgpilot

Thanks for the response. You're right, of course

I had everything within the while loop and could only get the first pass to go into the db. When I took the querry out of the loop, only the last pass would go in.

So only the first or the last pass, but nothing else.

Must need a loop / array / something...

Any thoughts ?

ppgpilot

More Info...

the error thrown is

mysql_fetch_array(): supplied argument is not a valid MySQL result resource

so... it makes one pass correctly, inserts into the db, then on the second and subsequent pass, the error above.

So why is the $sql=(insert... ) creating a problem in the second pass through the while loop?

Weedpacket

You shouldn't even need the loop: move the data in the database around inside the database.

INSERT INTO $tbl3
	(user_name, entry, day, item, amt, calorie, fat, fat_calories, carb, protein, sugar)
SELECT
	'$user_name', '$entry', '$day', item, amt, calorie, fat, fat_calories, carb, protein, sugar
FROM $tbl5
	where recipe_name = '$recipe_name'

Not forgetting to use [man]mysql_real_escape_string[/man]on anything that could conceivably contain apostrophes (which is basically $username, $entry, $day, and $recipe_name).

mariaak83

That's because you assign the results from the insert query to the same variable you used for the select query!

For statements like select mysql_query returns an array on success or false on failure. But for statements like insert, update etc mysql_query returns true/false! So you have an array with the results from the recipe db in $results and after the first loop this variable becomes TRUE and you get the error message.

ppgpilot

Weedpacket,

Great solution – I learned something… One issue…

…along with the recipe data entry, I also need to enter a user specified day into the day field in $tbl3 for each of the entries WHERE recipe_name = '$recipe_name'.

This will allow me to group these entries with other entries included on the same day reference (including individual items or other recipes).

I can't use time() default since the date the data is entered may not be the $day needed for the entry reference for future reference on other pages.

I did accomplish the $day inserition by adding a hack immediatly after the first $sql to enter the correct $day entry: since the initial day field defaults to 0 on data entry, and these entries should be the only ones with a 0 in the day field, I simply run an UPDATE:

the hack...


if($works1 > 0)
{
    $sql=("update $tbl3 set day = $day where day = '0'");
    $result = mysql_query($sql) or die(mysql_error());
    $works2 = mysql_affected_rows();
    require 'my_error.php';

}

It works, but there must be a better way to add the $day to the day field during the initial entry, but I can't see it... this is new territory for me.

Here is my latest code…

Thanks for your help!


$sql=("INSERT INTO $tbl3

(
user_name,
entry,
item,
amt,
calorie,
fat,
fat_calories,
carb,
protein,
sugar
)

SELECT

user_name,
entry,
item,
amt,
calorie,
fat,
fat_calories,
carb,
protein,
sugar

FROM $tbl5 where recipe_name = '$recipe_name'");


$result = mysql_query($sql) or die(mysql_error());
$works1 = mysql_affected_rows();



if($works1 > 0)
{
    $sql=("update $tbl3 set day = $day where day = '0'");
    $result = mysql_query($sql) or die(mysql_error());
    $works2 = mysql_affected_rows();
    require 'my_error.php';

}




if($works2 > 0) // entry complete
{
  $completed = 1;
}

Weedpacket

In the code I posted, the values for user_name, day, and entry were all user-specified (i.e., embedded in the insert statement by PHP).

ppgpilot

Whoa!

Didn't know you could do that!

Thanks Weedpacket for the education and putting an end to my agony!

For anyone finding this thread, the final code is below and works perfectly:


$sql=("INSERT INTO $tbl3

(
user_name,
entry,
day,
item,
amt,
calorie,
fat,
fat_calories,
carb,
protein,
sugar
)

SELECT

'$user_name',
'$entry',
'$day',
item,
amt,
calorie,
fat,
fat_calories,
carb,
protein,
sugar

FROM $tbl5 where recipe_name = '$recipe_name'");


$result = mysql_query($sql);
$works1 = mysql_affected_rows();




if($works1 > 0) // entry complete
{
  $completed = 1;
}

Weedpacket

ppgpilot wrote:
Didn't know you could do that!

The SELECT statement is an ordinary select statement - any expression that produces the result can appear in the list of returned values. Usually it's the name of a field in the table(s) being selected from, but it could also be a literal or a function call or some other calculation.