[FIXED] Database not receiving correct data

Issue

I'm trying to fill 3 different tables in my database.

1. Sale table which has the following rows:

   • sale_id
   • fk_sale_user
   • fk_payment_id
   • sale_date

2. Print_for_sale which has:

   • print_for_sale_id
   • fk_sale_id
   • price_print_for_sale

3. Print_has_size_has_print_for_sale

   • print_has_size_has_print_for_sale_id
   • fk_print_has_size_id
   • fk_print_for_sale_id

Here is a screenshot of my database in mysql workbench so you can situate.

I'm trying to make the "buying" action. This is my markup and php code:

printdetail.php

<?php 


$id= $_GET['print_id'];

$printdetails = getprintdetailsById($con, $id);

$line = mysql_fetch_assoc($printdetails);

$selectsize =" select * from size";

$result=mysql_query($selectsize);

?>


<div>
    <img width="800px;"src="<?php echo $line['print_img']?>"/>
</div>


<div>
    <span> <?php echo $line['print_title']?> </span>
    <form action="addprints.php" method="post">
        <ul>
            <?php while ($line = mysql_fetch_assoc($result)) { ?>
                <li> <input type="checkbox" name="sizes[]" value="<?php echo $line['size_id']?>">
              <?php echo $line['size_name']." Price: ".$line['size_price']."€ "?> 
                </li>
            <?php } ?>
            <input type="submit" value="Add to Cart" >
        </ul>
        <input type="hidden" name="print_id" value="<?php echo $id; ?>" />
    </form>
</div>

addprints.php

    <?php
   session_start();
   $user_id = $_SESSION['user_id'];
   print_r($_POST);

   $id_print= $_POST['print_id'];

   include('database.php');

   $bought_sizes=$_POST['sizes'];

   $number_of_bought_sizes= count($bought_sizes);


   //header('location:index.php?area=printstore');


   $sqlinsertsale = "insert into sale
   (fk_sale_user,
   fk_payment_id) 
       values(".$user_id.", 2)";

   mysql_query($sqlinsertsale); 


  for($i=0; $i< $number_of_bought_sizes; $i++){

   $selectsize = "select * 
   from size";

   $resultsize = mysql_query($selectsize);

   while($linesize = mysql_fetch_assoc($resultsize))
   { $size_price = $linesize["size_price"]; }


   $selectsale = "select * 
   from sale";

   $resultsale = mysql_query($selectsale);

   while($linesale = mysql_fetch_assoc($resultsale))
   { $sale_id = $linesale["sale_id"]; }


   $sqlinsertprintforsale = "insert into print_for_sale
       (fk_sale_id,
       price_print_for_sale) 
       values(".$sale_id.", ".$size_price.")";

   mysql_query($sqlinsertprintforsale);


   $selectprinthassize = "select * 
   from print_has_size";

   $resultprinthassize = mysql_query($selectprinthassize);

   while($lineprinthassize = mysql_fetch_assoc($resultprinthassize))
   { $print_has_size_id = $lineprinthassize["print_has_size_id"]; } 


   $selectprintforsale = "select * 
   from print_for_sale";

   $resultprintforsale = mysql_query($selectprintforsale);

   while($lineprintforsale = mysql_fetch_assoc($resultprintforsale))
   { $print_for_sale_id = $lineprintforsale["print_for_sale_id"]; }     


    $sqlinserthashas = "insert into print_has_size_has_print_for_sale
       (fk_print_has_size_id,
       fk_print_for_sale_id) 
       values(".$print_has_size_id.", ".$print_for_sale_id.")";

   mysql_query($sqlinserthashas);


   } 

   ?>

I am very new to php and mysql so I'm sorry if this is a dumb or bad question. I can't figure out what I'm doing wrong...

• The table Sale is being updated correctly in phpmyadmin. Everything is working. User ID is OK and payment ID is OK too. (I haven't done the payments part yet so I just used a number to test.)

The table Print_for_sale is updating the correct sale ID (fk_sale_id), however the price_print_for_sale is always the same, no matter what print I choose. It's always 150 when sometimes it should be 65 ou 25. (The print price is defined in the size table. So far I have 3 different sizes, so different prices.)

The table Print_has_size_has_print_for_sale is updating the correct print_for_sale ID, but the fk_print_has_size_id is always number 12 (which is the last from that list) and it has nothing to do with my choices on the form. I believe this is what is making the price come out wrong. If it's always the same combination of prints and size (print_has_size), then it's always going to have the same price... Why is this happening?

Can someone please help me?

Here are some screenshots of phpmyadmin:

Edit: This is the function I used:

<?php

function getprintdetailsById($con, $print_id){
    $question = "select * from print where print_id=".$print_id;

    $result = mysql_query($question, $con);

    return $result;
    }

  ?>

Solution

This is a lot of info.. I guess we can start debugging. Let's begin with your while loops. Inside your for loop, the first line select * from size, this should return an array, then you are iterating this array with your while loop, but you are assigning them to just one variable. This will overwrite the data result of the last iteration. Is this what you want?... to be continued.

You don't want that to be overwritten.. so what you need to do for that while loop is assign it to an array, like this:

while ($linesize = mysql_fetch_assoc($resultsize)) {
    $size_price[] = $linesize["size_price"];
}

so, once you have the $size_price[] with all the desired sizes, we move on.. your code now runs select * from sale where you want all the sale_ids from. So just like above, assign it to an array, we'll say $sale_ids[]

Now you are trying to run a query that inserts data to the print_for_sale table, but the data comes from the two different arrays you created above.. this is will not work, and if so, you would need to come up with crazy loops and iteration like you already have tried.

To fix it, you first need to look at your tables, assign them unique ids, and link them through indexes, once you do that, you need to use the JOIN SQL command on your queries to get the matched data together.

I would look into separating your code as well. this will help you reuse it. You should look into an MVC framework. Ever heard of Codeigniter? its very easy to learn and powerful for applications.

Hope this helps.

Answered By - CodeGodie