Hello,
Is there a way to splice an array? I need to splice if the first row is zero then the whole column will be splice.
[php]
$cars = array
(
array(“Cars”,0,18,2,4,0,3,0,8),
array(“BMW”,20,13,2,4,50,3,60,8),
array(“Saab”,10,2,2,4,20,3,70,8),
array(“Land Rover”,0,15,2,4,80,3,80,8)
);
echo ‘
| ’.$key.’ | ’;
What exactly have you been working on? All of your posts seem to point to a wrong approach to whatever you’re doing.
See the XY problem in my signature.
Hello Kevin,
It is probably a wrong approach but I am new in PHP and still learning. I need a report what will compute Reject from a mySQL database.
I already manage to show the rejects but I need to remove those with zero total.
What I did is grab the info from mySQL and place it to an 2d array(this is my first time to use 2d array). using foreach i display the 2d array but I need to slice first the zeros. I am hoping I have explain it well for I am not native in english.
I may be able to show it to you if you allow. Thank you for your help.
Roggie
Hoping there will be an easy way.
I am currently analyzing my code and maybe instead of placing the tables in an 2d array. I will place the HTML codes in a variable then echo it after.
Hmm…
Kindly advise a better way it is much appreciated.
Hello Kevin,
You are such a genius. I do not know how you knew it but yup you are correct. It is an XY problem.
If you allow me to explain.
I manage to get a slicer below
[php]
foreach(array_keys($array) as $key) {
unset($array[$key][1]);
}
[/php]
This actually slice the whole row of an array but my issue is the column number. It change every time I slice the array. Always getting this undefined offset.
I am getting nowhere. Below was my code using array:
[php]
$NGTable = Array();
$date_to1 = new DateTime(‘11/08/2016 00:00:00’);
$date_from1 = new DateTime(‘11/07/2016 00:00:00’);
$date_from = $date_from1->format(‘Y-m-d’);
$date_to = $date_to1->format(‘Y-m-d H:i:s’);
$result1 = mysqli_query($con,“SELECT * FROM lot WHERE date_create BETWEEN ‘$date_from%’ and ‘$date_to%’”);
if(! $result1){ echo “Line Number:” . LINE ; die('Cannot query error: ’ . mysqli_error($con)); }
//$result = mysqli_fetch_array($result1);
$LotIDMaxRow = mysqli_num_rows($result1);
//Need to get the max number of item in no_good
//echo count($result);echo “
”;
$MaxNGFetch = mysqli_fetch_array(mysqli_query($con,“SELECT MAX(id) FROM no_good”));
$MaxNG = $MaxNGFetch[‘MAX(id)’];
$row=1;
$ColSum = Array();
for ($col = 0; $col < $MaxNG+1; $col++) {
$ColSum[$col] = 0;
}
$NGTable[0][0] = “LOT”;
for ($col = 1; $col < $MaxNG+1; $col++) {
$query = mysqli_fetch_array(mysqli_query($con,“SELECT * FROM no_good WHERE id=$col”));
$NGTable[0][$col]=$query['ng_name'];
}$NGTable[0][$col+1] = “TOTAL”;
while ($LotID = mysqli_fetch_array($result1)) {
$RowSum = 0;
for ($col = 0; $col < $MaxNG+1; $col++) {
if ($col == 0){
$NGTable[$row][$col] = $LotID[‘proc’].$LotID[‘petsa’].str_pad($LotID[‘serye’], 3, ‘0’, STR_PAD_LEFT);
} else {
$NGQuery = mysqli_fetch_array(mysqli_query($con,“SELECT SUM(qty) FROM ng_logs
WHERE lot_id={$LotID[‘lot_id’]} AND ng_id=$col”));
if(! $NGQuery){ echo “Line Number:” . LINE ; die('Cannot query error: ’ . mysqli_error($con)); }
if($NGQuery[‘SUM(qty)’] === null){
$NGTable[$row][$col] = 0;
} else {
$NGTable[$row][$col] = $NGQuery[‘SUM(qty)’];
$RowSum += $NGQuery[‘SUM(qty)’];
$ColSum[$col] += $NGQuery[‘SUM(qty)’];
}
}
}
$NGTable[$row][$col] = $RowSum;
$row += 1;
}
$NGTable[$row][0] = “Total”;
//Adding the total below the table
$col=1;
$ColSumTot = array_slice($ColSum,1);
foreach($ColSumTot as $key) {
$NGTable[$row][$col] = $key;
$col++;
}echo “
”;
//Need to slice the array.
foreach(array_keys($NGTable) as $key) {
unset($NGTable[$key][1]);
}
//Time to create the table since our array is ready.
echo ‘’;
foreach( $NGTable as $NGTableShow )
{
echo ‘’;
foreach( $NGTableShow as $key )
{
echo ‘’;
}
echo '</tr>';
}
echo ‘
| ’.$key.’ |
Then I read again your message and focus on this word " wrong approach ". A sip of hot coffee and deleted my whole code
Rewriting again my code with a Different approach. I drop the 2d array I was dreaming and woke up to the reality of PHP, HTML and Array(not 2d).
Below was the correct code
[php]
$NGTable = Array();
$date_to1 = new DateTime(‘11/08/2016 00:00:00’); // This was changed.
$date_from1 = new DateTime(‘11/07/2016 00:00:00’); // This was changed.
$date_from = $date_from1->format(‘Y-m-d’);
$date_to = $date_to1->format(‘Y-m-d H:i:s’);
//update start here.========================================
//need to check which LTS has Reject
$NGTitleQuery = mysqli_query($con,“SELECT no_good.id as ngid, no_good.ng_name as ngname FROM lot
JOIN ng_logs ON lot.lot_id=ng_logs.lot_id
JoIN no_good ON ng_logs.ng_id=no_good.id
WHERE date_create BETWEEN ‘$date_from%’ and ‘$date_to%’
GROUP BY ngid”);
if(! $NGTitleQuery){ echo “Line Number:” . LINE ; die('Cannot query error: ’ . mysqli_error($con)); }
$NGNameArray = array();
while($row = mysqli_fetch_array($NGTitleQuery)) {
$NGNameArray[] = $row;
}
//Creating the table
$ColSum = Array();
$col = 1;
//The headers
echo “”;
foreach($NGNameArray as $NGName){
echo “”;
$ColSum[$col] = 0;
$col++;
}
echo “”;
//Now create the table body
$LotQuery = mysqli_query($con,“SELECT * FROM lot WHERE date_create BETWEEN ‘$date_from%’ and ‘$date_to%’”);
while($LotFetch = mysqli_fetch_array($LotQuery)) {
$NGTotalRow = 0;
$col = 1;
$LotNum = $LotFetch[‘proc’].$LotFetch[‘petsa’].str_pad($LotFetch[‘serye’], 3, ‘0’, STR_PAD_LEFT);
echo “
| LOT | ” . $NGName[“ngname”]. “ | Total |
|---|---|---|
| $LotNum | ”.$NGQty[‘SUM(qty)’]." | $NGTotalRow |
| Total | $key |
I also attached the output of with 2d array and no 2d array.
Once again. Thank you very much!!! You just simply inspired me. 
I still feel like there is something else going on.
I already manage to show the rejects but I need to remove those with zero total.
A zero or null value in the datatable?
Post an SQL dump of your database so I can recreate it on my end. This whole thing can be done in a query without all that code.
Thanks for the reply astonecipher . I was pertaining to the 2D array using slice method not in the database. Those zeros did not come from the database it was generated in the code.
Hi Kevin,
Thank for giving time however I studied mySQL dumping but I do not have access to terminal so I cannot dump. I am hoping this attach file can help that was exported via PHPmyAdmin.
Alternatively I can share my x10 account. It is a test account.
If possible kindly use procedural programing if available because object oriented is hard for me to read as of now.
This is my target report.
Thank you.
Backup.txt (75.6 KB)
Ok, got it. Now tell me exactly what you want to know from the database. Forget about any code.
Mr. Database I would like to know or see… (make sure to include relevant tables and column names)
You have no foreign keys so I can not easily tell what ties to where. You could really improve on your table and column names.
Because you had no foreign keys, your data is corrupt. Before you do anything, you need to get the DB design correct and fix the corrupt data. You have many lot_id = 0. There is no such thing as a lot_id = 0
You also have lot_id’s in tables that don’t exist in the lot table. I see qa_id in two tables but there is no primary key qa_id anywhere.
This will get you started with one table finding bad records.
[php]SELECT
quantity_usage.lot_id
FROM
quantity_usage,
lot
WHERE
quantity_usage.lot_id NOT IN (SELECT lot.lot_id FROM lot)[/php]
In my idea we will only be using three tables here. lot, ng_logs and no_good.
Mr. Database please show me the list of LOT Number (lot.proc+lot.petsa+lot.serye) base on date range (Nov 07 - 08, 2016) which shows total quantity of ng_logs.qty by ng_logs.ng_name. In the far right column should show the total of NG qty per lot and bottom row should show the total NG qty by ng_name. That’s it. This should show similar below:
You have no foreign keys so I can not easily tell what ties to where. You could really improve on your table and column names.Because you had no foreign keys, your data is corrupt. Before you do anything, you need to get the DB design correct and fix the corrupt data. You have many lot_id = 0. There is no such thing as a lot_id = 0
You also have lot_id's in tables that don't exist in the lot table. I see qa_id in two tables but there is no primary key qa_id anywhere.I have asked my colleague about this. He said this is a number of user.id. We will somehow use this for audit so we will know who is user did the process. This is similar to tact_time.operator_id. He should have used operator_id instead or better yet we have used user_id. Sorry for this one.
This will get you started with one table finding bad records. [php]SELECT quantity_usage.lot_id FROM quantity_usage, lot WHERE quantity_usage.lot_id NOT IN (SELECT lot.lot_id FROM lot)[/php]Wow this is nice code. I did not know this is possible to put 2 SELECT in a query. I have use it and seen the lot_id in quantity_usage which no longer exist in the table 'lot'. anyway, these are actually ok since we are using test database.
Hello Sir Kevin,
This Foreign key is good for security. This also help preventing other records orphan. I will be including this when designing a database. Thanks a lot for the food in my brain. This week sure gain a lot of knowledge,
May I ask the following:
Thanks.
Roggie
In order to set the foreign keys with existing data, you MUST NOT have orphan records. It will just give you an error.
The keys have nothing to do with Php.
Yes, you can set more than one key in a table
Keys are set in child tables. Example: Parent table is lot with primary key lot_id. Every other table with lot_id should have a foreign key set on that column to lot.lot_id
Set better table and columns. No one looking at this has any idea what ng means. In a few years of not looking at the code, you won’t either.
After all this is done, post your new DB dump and I will look at it.
We will somehow use this for audit so we will know who is user did the process.
You might want to think about auditing tables using Triggers. Wouldn’t you rather know not only who took an action but also what the changes were?
Hello Kevin,
I did all the table and placed a foreign key except for one table. The one you are saying with LOT=0. We are still checking how can we get around with it. These are raw material quantity stock which were not process in the line. They are zero lot_id because they have no lot_id. with this, I cannot make a foreign key. I was thinking of making another table for this zero lot_id. But doing this will affect a lot in the PHP code. Still thinking a way to fix this.
Anyway as we implement this Foreign key we have seen numbers which are not suppose to be there. This foreign key really help us a lot. Please give us time to fix this then I will get back to you on the new database with foreign key.
Thank you.
Roggie
Add a value to the LOT table that says it is raw material. Adding another table with the same relationship will just cause a headache when working on the ERD
The one you are saying with LOT=0. We are still checking how can we get around with it. These are raw material quantity stock which were not process in the line. They are zero lot_id because they have no lot_id.
Then you still have design issues. I don’t know about what you are doing to advise but the answer is going to be simple.
What timezone/country are you in? Perhaps we could do a quick skype session to get you straightened out. If you are in the US you could probably call.
A detailed description of exactly what this app does would be helpful along with what the cryptic table and column names mean
Here is a generic start. You still need to get the DB design right FIRST. (Get rid of those cryptic names)
[php]SELECT
lot.lot_id,
Concat(lot.proc, lot.petsa) AS Lot,
no_good.ng_name,
lot.serye,
ng_logs.qty As Total
FROM
lot
LEFT JOIN ng_logs ON ng_logs.lot_id = lot.lot_id
LEFT JOIN no_good ON no_good.id = ng_logs.ng_id[/php]
We are working on a customize inventory system.
What timezone/country are you in? Perhaps we could do a quick skype session to get you straightened out. If you are in the US you could probably call.I am in the other side of the earth. GMT+8 Yes we can do a Skype session but I am not native in English. I do not speak English very well. We can schedule it if you want to. I send my skype name offline. thanks in advance.
A detailed description of exactly what this app does would be helpful along with what the cryptic table and column names meanSorry about that cryptic table. Let me decyrpt it for you. There are the following: 1. no_good = reject 2. ng_name = reject code name like what is the name why it was rejected. 3. uom = unit of measure 4. proc = process type code. currently there are 4 of these. LS = light source; CA = Case assembly; FA=Final Assembly; WB=Wire bonding. 5. Petsa = our native language means date. this is in yymmdd format. 6. serye = our native language means series. at the end of proc+petsa there is a series. This start from 1 every first issue of the month. if the last LOT issue this october is LS-161030-039(proc-petsa-serye) then on Nov it will be reset LS161103001.
Should there be any please do advice.
Should there be any please do advice.
Do the things I already told you and we can go from there,
