PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Wed Aug 12, 2020 12:30 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 13 posts ] 
Author Message
PostPosted: Sun Sep 25, 2016 5:40 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
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.

Top
 Profile  
 
PostPosted: Sun Sep 25, 2016 7:25 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
You could make contents its own table with a foreign key pointing back to this table.

_________________


Top
 Profile  
 
PostPosted: Sun Sep 25, 2016 7:39 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
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?


Top
 Profile  
 
PostPosted: Sun Sep 25, 2016 7:44 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada

_________________


Top
 Profile  
 
PostPosted: Sun Sep 25, 2016 8:29 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
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 :)


Top
 Profile  
 
PostPosted: Sun Sep 25, 2016 11:48 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
Hmm why is this not working?

Syntax: [ Download ] [ Hide ]
 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?


Top
 Profile  
 
PostPosted: Sun Sep 25, 2016 11:52 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
Typically you prepare the statement once and then iterate over it to insert multiple rows.

_________________


Top
 Profile  
 
PostPosted: Sun Sep 25, 2016 11:54 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
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.

_________________


Top
 Profile  
 
PostPosted: Sun Sep 25, 2016 12:07 pm 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
Ok I changed the code to

Syntax: [ Download ] [ Hide ]
            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)


Top
 Profile  
 
PostPosted: Sun Sep 25, 2016 12:45 pm 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
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?

returns a boolean, which you can capture. If it's false, you can check

_________________


Top
 Profile  
 
PostPosted: Sun Sep 25, 2016 1:51 pm 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
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

Syntax: [ Download ] [ Hide ]
            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...


Top
 Profile  
 
PostPosted: Sun Sep 25, 2016 2:31 pm 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
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:

Syntax: [ Download ] [ Hide ]
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:

Syntax: [ Download ] [ Hide ]
<?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


Top
 Profile  
 
PostPosted: Mon Sep 26, 2016 12:12 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
Ok, got it to work. I had a primary index on another column in the DB and when I removed that it worked :)


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 13 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: Google [Bot] and 43 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group