Best way to handle unnkown number of srtings to DB? (SOLVED)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
hybris
Forum Contributor
Posts: 172
Joined: Wed Sep 25, 2013 4:09 am

Best way to handle unnkown number of srtings to DB? (SOLVED)

Post by hybris »

Hi again :)

this time I'm trying to figure out how to best report the contents of a truck but I don't know which would be the best way.


When someone report he sends a truck he will be presented with a window with say 3 columns and 10 rows.

The columns will be ArtNo | ArticleName | Qty

At the bottom of the page there will also be a button "ADD ROW" That adds a new empty row to the bottom if he ships more than 10 articles.

So one time the truck might only hold 3 items and the next time 80 articles.

Which would be the best way to store this in a database?

I was thinking of saving all the content of the truck to a database as a text field and use implode explode to work with the content. The DB would look something like this
TruckID|Departuredate|ArrivalDate|Contents (textfield).

The content textfield would look something like this:
1234|articlename1|12|2345|articlename2|25 and so on...

This will do what I want but is there a better way?
(I guess i need to make sure the user don't leave any empty fields in the middle or maybe use 2 different signs when using explode..one column separator and one row separator...)

Thanks :)
Last edited by hybris on Mon Sep 26, 2016 12:12 am, edited 1 time in total.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Best way to handle unnkown number of srtings to DB?

Post by Celauran »

You could make contents its own table with a foreign key pointing back to this table.
hybris
Forum Contributor
Posts: 172
Joined: Wed Sep 25, 2013 4:09 am

Re: Best way to handle unnkown number of srtings to DB?

Post by hybris »

So that each shipping generate a new DB table?

I thought of that but I only have 1 DB to play around with and after a few 100 shippings it would be hard for me to find my other tables and if would delete the tables after shipment arrived I still would need to save it somehow for traceability...or did i misunderstand you?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Best way to handle unnkown number of srtings to DB?

Post by Celauran »

hybris wrote:So that each shipping generate a new DB table?
No, no. Nothing like that. You'd have two tables, one tracking the trucks themselves (or maybe trips?), and one tracking the contents. That's it.

So trips (or whatever that table is called) would contain: truck_id, departure, arrival
Then contents table would contain trip_id (which matches the id from the trips table), article_number, article_name, quantity
hybris
Forum Contributor
Posts: 172
Joined: Wed Sep 25, 2013 4:09 am

Re: Best way to handle unnkown number of srtings to DB?

Post by hybris »

Lol I'm stupid,

yeah that would be a nice solution. Thats bacically how i set up my other tables so I'm amazed i didnt come up with this idea myself lol.

Thanks :)
hybris
Forum Contributor
Posts: 172
Joined: Wed Sep 25, 2013 4:09 am

Re: Best way to handle unnkown number of srtings to DB?

Post by hybris »

Hmm why is this not working?

Code: Select all

 for($i = 1; $i <= $NumberofRows ; $i++){
                       
            if($insert_stmt = $mysqli->prepare("INSERT INTO RegisterCargo (sID, artNo, lotNo) VALUES (?, ?, ?)")) {
              $insert_stmt->bind_param('iss', $tID, $_POST['artNo'.$i], $_POST['lotNo'.$i]);
              $insert_stmt->execute();
            }
        }
It only adds the first row.

If I print the array I see it stored all lines as artNo1 = aaa, artNo2 = bbb and so on..
If I loop and print the post values it works but when i try to insert in DB only the first line is added??

Edit: Also this does alot of rapis writes to the db (if it should work)..is there a better way to do this?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Best way to handle unnkown number of srtings to DB?

Post by Celauran »

Typically you prepare the statement once and then iterate over it to insert multiple rows.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Best way to handle unnkown number of srtings to DB?

Post by Celauran »

That aside, if it's only inserting one row, are any errors being reported? Have you checked the error log? Things don't typically fail silently.
hybris
Forum Contributor
Posts: 172
Joined: Wed Sep 25, 2013 4:09 am

Re: Best way to handle unnkown number of srtings to DB?

Post by hybris »

Ok I changed the code to

Code: Select all

            if($insert_stmt = $mysqli->prepare("INSERT INTO RegisterFPCargo (sID, artNo, lotNo, productionDate, bestbeforeDate, qty) VALUES (?, ?, ?, ?, ?, ?)")) {
              for($i = 1; $i <= $NumberofRows ; $i++){    
                $insert_stmt->bind_param('issssi', $tID, $_POST['artNo'.$i], $_POST['lotNo'.$i], $_POST['pDate'.$i], $_POST['bbDate'.$i], $_POST['qty'.$i]);
                $insert_stmt->execute();
              }
            }
            echo $tID;
I have error msg enabled so when i run this it display the echo$tID; at the end properly (added just for debug reasons).
No error msg is displayed. Where can i find the error log? (Im at one.com)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Best way to handle unnkown number of srtings to DB?

Post by Celauran »

I don't know about log files from one shared host to another. They can be difficult to find or unavailable entirely. What about on your local machine? Is it working as expected there?

mysqli_stmt::execute returns a boolean, which you can capture. If it's false, you can check mysql::error
hybris
Forum Contributor
Posts: 172
Joined: Wed Sep 25, 2013 4:09 am

Re: Best way to handle unnkown number of srtings to DB?

Post by hybris »

if i do $Bol=$insert_stmt->execute();
and echo $Bol;
it writes 1 and then nothing so I guess it means its true for the first iteration and then null?


Ok so I was in contact with support and the only thing they could see is
Directory index forbidden by Options directive:
and a link to the script

Code: Select all

            if($insert_stmt = $mysqli->prepare("INSERT INTO RegisterFPCargo (sID, artNo, lotNo, productionDate, bestbeforeDate, qty) VALUES (?, ?, ?, ?, ?, ?)")) {
              for($i = 1; $i <= $NumberofRows ; $i++){    
                $insert_stmt->bind_param('issssi', $tID, $_POST['artNo'.$i], $_POST['lotNo'.$i], $_POST['pDate'.$i], $_POST['bbDate'.$i], $_POST['qty'.$i]);
                echo $_POST['artNo'.$i]."<br>"; //Debug
                $insert_stmt->execute();
                
              }
            }
            echo $tID;
I added echo $_POST['artNo'.$i]."<br>"; before the execute statement and it writes all artNo correcly to my screen... It adds the first iteration $i = 1 correctly to the database...

Im starting to wonder if this is some anti hacker thing... i guess their servers would be unhappy if i made a script for i = 1 to 999999999999999999999999 insert something in DB...
hybris
Forum Contributor
Posts: 172
Joined: Wed Sep 25, 2013 4:09 am

Re: Best way to handle unnkown number of srtings to DB?

Post by hybris »

This is driving me insane.. I added some code so if first artno is empty then it should skip it and add the next row instead...so if i leave the first row blank it stores the second row to the DB.. This indicates that my $i loop is working..

So why does it only save 1 row???

New code:

Code: Select all

if($insert_stmt = $mysqli->prepare("INSERT INTO RegisterFPCargo (sID, artNo, lotNo, productionDate, bestbeforeDate, qty) VALUES (?, ?, ?, ?, ?, ?)")) {
              for($i = 1; $i <= $NumberofRows ; $i++){ 
                if($_POST['artNo'.$i] <> "") { 
                $insert_stmt->bind_param('issssi', $tID, $_POST['artNo'.$i], $_POST['lotNo'.$i], $_POST['pDate'.$i], $_POST['bbDate'.$i], $_POST['qty'.$i]);
                echo $_POST['artNo'.$i]."<br>"; //Debug
                $insert_stmt->execute();
                }
              }
            }
            echo $tID;

the full code of the page:

Code: Select all

<?php
include_once(__DIR__."/../LoginSystem/Includes/DBConnect.php");
include_once(__DIR__.'/../LoginSystem/Includes/FunctionsToInclude.php');
sec_session_start(); 

if(login_check($mysqli) == true) { 
 require(__DIR__.'/../Mainpage/newpage_lib.php');
 require(__DIR__."/../LoginSystem/Includes/DBConnect.php");
 date_default_timezone_set("Europe/Stockholm");
$CurrentDateTime=date("Y-m-d H:i:s"); //create date time
$Current=date("Y-m-d"); //Används till mindate
$User=$_SESSION['userName'];
$UserID=$_SESSION['userID'];
$Language="Swedish";
$Submitted=$_POST['Submitted'];
$NumberofRows=20;
if (!$mysqli->set_charset("utf8")) { } //Sätt DB charset till utf8.
      
     if ($stmt = $mysqli->prepare("SELECT 
     cID, userJob, userGroup FROM Users WHERE userID = ?")) { 
      $stmt->bind_param('i', $UserID);
      $stmt->execute(); 
      $stmt->bind_result($UsercID, $UserJob, $UserGroup);
      $stmt->store_result();
      $stmt->fetch();
      $stmt->close();
     }
     
    if ($UserGroup == "Admin"){ 
        $homepage = new Page();
        ob_start();
        if (!isset($Submitted)) {
        ?>
        <table>
            <form id="RegisterRPShipping" name="RegisterRPShipping" form accept-charset="utf-8" method="post" action="RegisterRPShipping.php"> 
                <tr>
                    <td style="background-color: #80ffff"; FONT COLOR="#000000"; colspan="2";><b>Report shipped ready product</b></td>
                </tr>
                <tr>
                    <td style="background-color: #b3ffff"; FONT COLOR="#000000"; colspan="2";><i>This action reports the departure of products from the plant.</i></td>
                </tr>
                    
                <tr>
                 <td style="background-color: white;"><FONT COLOR="#000000"><b><?php echo "The Truck ID or Container Number:";?></b></FONT></td>
                 <td style="background-color: white;"><input name="shipmentID" type="text" id="shipmentIDID" style="width:300px; height:18px;" value="" required /></td>
                </tr>
                <tr>
                 <td style="background-color: white;"><FONT COLOR="#000000"><?php echo "This is a:";?></FONT></td>    
                 <td style="background-color: white;"><select name="shipmentType" id="shipmentTypeID" style="width:300px; height:18px;">
                    <option value="0" selected>Truck</option>
                    <option value="1">Container</option>
                    <option value="2">Other Transport</option>
                 </td>
                </tr>
                 <tr>
                 <td style="background-color: white;"><FONT COLOR="#000000"><b><?php echo "Expected Arrival Date:";?></b></FONT></td>
                 <td style="background-color: white;"><input name="arrivalDate" type="date" id="arrivalDateID" min="<?php echo $Current;?>" style="width:300px; height:18px;" value="" required /></td>
                </tr>
                
                <tr>
                    <td style="background-color: #b3f40f"; FONT COLOR="#000000"; colspan="2";><b>Content.</b></td>
                </tr>
                <tr>
                    <td style="background-color: #b3f40f"; FONT COLOR="#000000"; colspan="2";><i>Please add the content of the Truck / Container.</i></td>
                </tr>
        </table>
        <table>
                <tr>
                    <th style="background-color: white; width:50px;"><FONT COLOR="#000000"><b><?php echo "ArtNo.";?></b></FONT></td>
                    <th style="background-color: white; width:100px;"><FONT COLOR="#000000"><b><?php echo "LotNo";?></b></FONT></td>
                    <th style="background-color: white; width:120px;"><FONT COLOR="#000000"><b><?php echo "ProductionDate";?></b></FONT></td>
                    <th style="background-color: white; width:140px;"><FONT COLOR="#000000"><b><?php echo "BestBeforeDate";?></b></FONT></td>
                    <th style="background-color: white; width:120px;"><FONT COLOR="#000000"><b><?php echo "Qty (Kg)";?></b></FONT></td>
                </tr>
                <?php
                for($i = 1; $i <= $NumberofRows ; $i++){
                ?>
                <tr>
                    <td style="background-color: white;"><input name="<?php echo "artNo".$i ?>" type="text" id="<?php echo "artNoID".$i ?>" style="width:50px; height:18px;" value="" /></td>
                    <td style="background-color: white;"><input name="<?php echo "lotNo".$i ?>" type="text" id="<?php echo "lotNoID".$i ?>" style="width:100px; height:18px;" value="" /></td>
                    <td style="background-color: white;"><input name="<?php echo "pDate".$i ?>" type="date" id="<?php echo "pDateID".$i ?>" style="width:140px; height:18px;" value="" /></td>
                    <td style="background-color: white;"><input name="<?php echo "bbDate".$i ?>" type="date" id="<?php echo "bbDateID".$i ?>" style="width:140px; height:18px;" value="" /></td>
                    <td style="background-color: white;"><input name="<?php echo "qty".$i ?>" type="number" id="<?php echo "qtyID".$i ?>" min="2016-09-11" style="width:100px; height:18px;" value="" /></td>
                </tr>
                <?php } ?>
                <tr>
                 <td  style="background-color: white;"><b>&nbsp;</b></td>
                </tr>
                
                <tr>
                 <input type="hidden" name="Submitted" value="TRUE"/>    
                 <td  style="background-color: white;"><input type="submit" name="Submit" value="<?php echo "Send"; ?>"/> </td>
                </tr>
            </form>     
        </table>   
        <?php
        } else { //Efter att man tryckt på post knappen
      
      //print_r($_POST);
      
        $shipmentID=$_POST['shipmentID'];
        $shipmentType=$_POST['shipmentType'];
        $arrivalDate=$_POST['arrivalDate'];
        $didArrive = false;
        //$Current
        
        
        //Spara trippen i databasen
        //Ta reda på tripID
        //Lagra innehållet i innehållsdatabasen med tripid som key
            
        // Spara trippen i databasen och ta reda på högsta(ID)
           if (!$mysqli->set_charset("utf8")) {}            
         
            if($insert_stmt = $mysqli->prepare("INSERT INTO RegisterTransport (transportID, transportType, departureDate, arrivalDate, didArrive) VALUES (?, ?, ?, ?, ?)")) {
              $insert_stmt->bind_param('sissi', $shipmentID, $shipmentType, $Current, $arrivalDate, $didArrive);
              $insert_stmt->execute();
              
                         if($stmt = $mysqli->prepare("SELECT tID FROM RegisterTransport ORDER BY tID DESC LIMIT 1") ){
                            $stmt->execute();
                            $stmt->store_result();
                            $stmt->bind_result($tID);
                            $stmt->fetch();
                         }
              } else { die("Database is down. Shipment was not saved. Please try again later.");}
              
        

            if($insert_stmt = $mysqli->prepare("INSERT INTO RegisterFPCargo (sID, artNo, lotNo, productionDate, bestbeforeDate, qty) VALUES (?, ?, ?, ?, ?, ?)")) {
              for($i = 1; $i <= $NumberofRows ; $i++){ 
                if($_POST['artNo'.$i] <> "") { 
                $insert_stmt->bind_param('issssi', $tID, $_POST['artNo'.$i], $_POST['lotNo'.$i], $_POST['pDate'.$i], $_POST['bbDate'.$i], $_POST['qty'.$i]);
                echo $_POST['artNo'.$i]."<br>"; //Debug
                $insert_stmt->execute();
                }
              }
            }
            echo $tID;
        }
        $content=ob_get_contents();
        ob_end_clean();
        $homepage->content = $content; 
        $homepage->Display();
    } else { echo 'You are not authorized to Add or Change Company information. Please contact Your administrator.'; } 
} else { echo 'You are not authorized to access this page, please login.'; }
?>
celauran wrote: mysqli_stmt::execute returns a boolean, which you can capture. If it's false, you can check mysql::error

How would i do that in the above code? I tried

if (!$mysqli->error) {
printf("Errormessage: %s\n", $mysqli->error);
}

but it didnt print anything...?

Please help me before my keyboard will pass trough my monitor :)

Thanks
hybris
Forum Contributor
Posts: 172
Joined: Wed Sep 25, 2013 4:09 am

[Solved] Best way to handle unnkown number of srtings to DB?

Post by hybris »

Ok, got it to work. I had a primary index on another column in the DB and when I removed that it worked :)
Post Reply