684425
-
Posts
66 -
Joined
-
Last visited
Posts posted by 684425
-
-
On 5/19/2021 at 5:31 PM, mac_gyver said:
example -
<?php // index/pivot the data // note: if you are using the PDO extension, there's a fetch mode that will do this for you $data = []; while($row = whatever_fetch_statement_you_are_using) { $data[ $row['status_id'] ][] = $row; } // define output heading information $headings = []; $headings[1] = ['label'=>'Heading for status id 1 section']; $headings[2] = ['label'=>'Heading for status id 2 section']; // produce the output foreach($headings as $key=>$arr) { // start a new section echo "<h3>{$arr['label']}</h3>"; if(!isset($data[$key])) { echo "<p>NO DATA</p>"; } else { foreach($data[$key] as $row) { // reference elements in $row to produce the output... echo '<pre>'; print_r($row); echo '</pre>'; } } // any code needed to finish a section goes here... }
Thanks for guiding sir. I have one problem that my script is divided in multiple files. i will have to check if i can apply it there.
-
I am trying to get rows from MYSQL table (attachment) based on:
- Select last five rows where status_id = 1
- Select last two rows where status_id = 2
*I did use UNION with two SELECT queries to get whole data as one array.
Now on PHP side...
I have first block for rows having status_id 1 so if there are rows having status_id 1 then PHP should display the data otherwise if there is no row having status_id 1 then PHP should print NO DATA only once.
I have second block for rows having status_id 2 so if there are rows having status_id 2 then PHP should display the data otherwise if there is no row having status_id 2 then PHP should print NO DATA only once.
I did use foreach loop then within loop i did use if condition to check status_id of rows. it works fine when i omit NO DATA part but when i add it. the result shows both rows and NO DATA in first block when there are one or more rows having status_id 1 but no row having status_id 2
Which made me believe that i was doing it the wrong way.
Please guide me
*PS: I know its PHP section the thread should be related to PHP problem but... is it right to use UNION in query? or should i post a new thread in relevant section for guidance?
-
Is there any function in php lib that does the following
$a = 1238; $n = 48; // 1*2*3*8or I will have to create one for my script?
(I want to do it for both client and server sides based on request sent by user. I actually want it in php that is why i asked it here, but if the solution (as function) is available in javascript (not jQuery) please share or guide me.)
-
10 minutes ago, Barand said:
I was trying it with a subquery and got confused.
Thank you 🙂
-
I have a column (unix_values) in table where multiple values are stored in unix timestamp which i want to convert into date yyyy-mm-dd and copy to another column (date_values) using single query
Any help please?
-
4 hours ago, maxxd said:
Sure, but how do you connect the invoice to the customer? Again, I assume that your customers can have more than one invoice throughout all of history, so that's the join you need - you need to link customer ID to customer ID between the tables, not customer ID to invoice ID.
Agree with you sir but the problem here is that DB is already created and filled with data. I tried to move data to a new DB and i was discussing the matter here. In the middle i was stopped from doing this, so i have to continue with the old one.
Here in my first post i pasted a sample of only those fields on which i wanted to run queries like as query total amounts paid by each customer, find number of customers whose total payments are (1. less than given input, 2. equal to given input, 3. greater than given input)
What i am facing here is, old DB is not normalized, also (dynamic?) values calculated from other values are stored in tables (the exact term for calculated is slipped from my mind)
The image in this reply is corrected one (in my opinion, not in the DB i am working on)
Here invoices.id is primary key in invoices table and installments.invoiceid id foreign key in installments table. i created these two tables (invoices and installments) and inserted exactly same data, then ran the following query and it worked. I just wanted to confirm that, is this query correct for any situation limited to these two tables? or is there something that i missed?
SELECT invoices.invoice, invoices.downpay, SUM(installments.payments) AS paid FROM invoices LEFT JOIN installments ON invoices.id = installments.invoiceid GROUP BY invoices.id
I also want to apologize. I am very sorry for creating this mess and confusing all of you. I hope that i will be forgiven by all the contributors who tried to help me here.
-
1 hour ago, maxxd said:
This is suspect. I assume the 'XXX (FK)' column in your initial post is actually customerId - you'll want to join on that unless your business logic is that each customer can only have one invoice in the system, ever.
1. You are right sir. this column should be named as customerid but it is named as invoice and i am not allowed to change it.
2. There is only one duplicate in history data but that account is closed. After that every customer is assigned a unique invoice.
-
Sir, i have reached at this
SELECT customers.invoice, customers.downpay, SUM(installments.payments) AS paid FROM customers LEFT JOIN installments ON customers.id = installments.invoices GROUP BY customers.id
Please guide me if i am wrong
-
24 minutes ago, benanamen said:
1. If those are your real column names you need to change them to something meaningful. Nobody knows what BBB or YYY or CCC means.
2. You likely have a DB design problem that needs to be normalized.Tell us about this data and what it represents.
Sorry sir, it is...
TableA is customers (id, invoice, downpay)
TableB is installments (id, invoices, payments)
TableC is the result of query that i want to run on both customers and installments
Sir i am not allowed to modify DB structure and i do not want to do that because it already is filled with lots of records. I can run only queries on data.
-
In another query i will need to repeat the same and then also i will have to count records from result where BBB + CCC = 10000. Please guide me🙂
-
I have two tables as shown in image, I want to join sum of records from table B based on keys in both tables to get the desired result as mentioned in third table. Please guide🙂
-
8 hours ago, 684425 said:
Currently i am working on Excel, i will post screenshots tonight in next reply IA.
I have added an image,
User requirements are:
Vendors, items by vendors and payments to vendors
How many vendors are there?
Which vendor has sold him what on which date (and what is the status of registration, file, copy and plates)?
How much amount (each transaction by date) has been paid to a specific vendor and what is the current balance?
Clients, items by invoice number and payments collection from clients
Which client has bought of which vendor's item on which date?
Who handled the client, filled the form and received advance from client? (There are three persons, user, his father, his brother. Form filling and advance receiving must be done by the same person)
Which client has paid all the amount? (in full or in parts) the status must be shown as cleared for this category
Which client is still paying installments? (is he/she paying on time or not? if yes then status shown as pending else defaulter)
If a mistake by one of those who handle clients, is found then status should be as error
Daily, weekly, monthly, yearly etc summary of collected payments can be generated
(I may have missed something, i will add it later. Also i will try to complete database structure and upload soon IA)
-
3 hours ago, Barand said:
A might be the seller but in another transaction A might be the buyer
No sir, A are the companies (always sellers), C are individuals (always buyers)
3 hours ago, Barand said:Don't store derived data, such as total paid, balance outstanding etc.
Agree sir, I am confused in both database structure and frontend, so i added this. But it is not added now.
Currently i am working on Excel, i will post screenshots tonight in next reply IA.
-
2 hours ago, Barand said:
Your items table may be a headache.
Vendor B sells bikes and the item attributes are
Now what if vendor D sells fridge/freezers whose attributes are
width, height, depth, colour, fridge capacity, freezer capacity, ice dispenser(Y/N)
and vendor E is selling concert tickets?
Sir, this is only for bikes. I am sorry i forgot to mention it in my post.
-
3 hours ago, requinix said:
What fields do you think should go in which tables, and what fields are you not sure about?
Sir, I think it is...
1. Vendors table
(vendor ID, name, contact)2. Items table:
(Item ID, invoice number 'varchar', Here every item is a bike 'varchar', different brands but duplicates 'varchar', different models but duplicates 'year', only two colors mostly red so duplicates 'varchar', engine number unique 'int', chasis number unique 'int', registration number unique 'varchar', statuses of registration, file, copy and plates are as 1 of 3 or 1 of 4 choices (radio buttons will be used on frontend), deal date 'date', delivery date 'date'3. purchase table:
(vendor ID, item ID, cost (separate for each bike))4. payments (to vendor) table:
(ID, vendor ID, payment amount, payment date, paid by)5. Clients table:
(ID, name, ID card number, contact number)6. Sales (to clients) table:
(ID, vendor ID, item ID, invoice number (not blank in this table but one record in history data is duplicate), date of buying, price, advance, paid to, total paid (including installments), total balance)7. installments (from clients) table:
(ID, sale ID, amount per month, paid amount (can be less, equal or more than per month), remaining (can be less than, equal to or greater than zero), payment month (on which it was to be paid), payment date, paid to)I am trying hard to make a database, compatible with manual data and old database too (old database contains errors but my friend is satisfied with it as it is the exact copy of his manual data) but on the other side i want to do things the right way (i think the fields in each table are not as they should be)
-
I am working on a database (filled with history data) for the following,
Person A is running a business as a third party between multiple sellers B and multiple buyers C1) A purchases bikes from B and maintains the following data:
"Invoice ID of C (or blank), Name of B" , "Bike brand, model(year), color(red or black), engine number, chasis number, registration number, registration status (1 of 3 posibilities), file status (1 of 4 posibilities), copy status (1 of 4 posibilities), number plates status (1 of 4 posibilities), price, deal date, delivery date"
2) A sells bikes to C in installments and maintains the following data:
"Invoice ID (not blank but one duplicate found in history data), Name of C, contact number, ID card number", "Deal date, Price, advance, advance taken by, remaining amount after advance", "Installment number, collection month, date on which it was collected, collected by, total collected amount including advance, total balance amount"
I was given this task from point number 2 but after facing problems specially in expenses and balance sheet. now i have to start from zero.
The idea that (in my opinion) suits the above is,
1. Vendors table
2. Items table
3. purchase table
4. payments (to vendor) table
5. Clients table
6. Sales (to clients) table
7. installments (from clients) table
But the problem i am facing is that i am failed to distribute the above fields between these tables. Please guide me if i am wrong at some point
-
22 hours ago, mac_gyver said:
you have stated/implied that the SELECT query in the first post is being executed/working. how do you know it's working
i forgot to answer this.
Most of the queries in the project execute at the time of page load and these pages display expected data accurately that comes from the database. The session variable error that i am getting is coming from one page only but that page too is displaying exactly the same data which is stored in database.
PS: Right after my previous reply, I did wrap my code in a condition to confirm that the session variable is set or not and used a redirect if it is not set. Php redirected me to the other page and told that the variable is not set. The other mistake that i have found is that i was setting one session variable with same name twice on two different pages. (First page is storing an array of lets say three elements ABC in a session variable and the other page is storing an array of "Those three elements" ABC "again with three more elements" DEF in the same session variable)
-
21 hours ago, mac_gyver said:
the error you are getting is a follow-on error, due to...
Thank you Sir for this detailed answer and your advice. I had a doubt that i was going wrong somewhere in this project but each of your word is telling me that i should study php more because there is a bunch mistakes in my code (but i am really surprised that the code is still working as expected. Sorry about repeating this again)
(i am not a proper literate in programming because computer science or programming was not my subject in school and i was unable to continue my studies due to more than ten years of unemployment. its just that i am crazy about learning it. your reply has told me that i am not ready yet for working as a programmer, even for my own experiments like this one. programming is not my profession, it is my craze)
Thanks to all of the experts on this forum for guiding me and others too. i am sure that your guidance will improve my knowledge 🙂
-
5 hours ago, gw1500se said:
I think fetch returns false only on failure. A query can be successful but return no rows. You might want to check to that condition.
I did check it and each of the queries are working as they should. Everything else is perfect even session values are set and working on every page. but still i am getting this session index notice that it is not defined.
-
On 6/17/2020 at 2:05 PM, requinix said:
That will add the $newArray array into the users array under a "0" key. As in $_SESSION[users] will be an array with an "id", "fname", "lname", and "0".
The answer is array_merge/replace or careful use of the +/+= operator.
Sir if i choose array_merge then how can i add new items to the existing session array?
i mean do i need to consider the existing session array as temporary session array and also i have to store new session values to a new temporary session array and then merge those two temporary session arrays into one new session array?
Existing session array created on one page and the session is set
$_SESSION['users'] = array( "id" => $row['id'], "fname" => $row['ufname'], "lname" => $row['ulname'] );
New session values on another page that i need to add to the existing session array
"boss" => $row['bossid'], "tasks" => $row['tasks'], "timeframe" => $row['tframe']
I was thinking to choose array_push() for this but your reply confused me sir🙂
-
7 minutes ago, gw1500se said:
What does 'single' do and return?
it extracts a single row from the database (i have pasted the whole class named model in model.php in my previous post and the below function is in the last of that class)
public function single(){ $this->execute(); return $this->stmt->fetch(PDO::FETCH_ASSOC); }
-
5 hours ago, gw1500se said:
I don't see where that variable is set anywhere.
public function verify(){ // Sanitize POST $post = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING); if($post['submit']){ // Compare $this->query('SELECT * FROM users WHERE username = :user'); $this->bind(':user', $post['username']); $row = $this->single(); if($row){ $_SESSION['isusers'] = true; $_SESSION['users'] = array( "id" => $row['id'], "name" => $row['name'] ); header('Location: '.ROOT_URL.'messages'); } else { Messages::setMsg('Incorrect username', 'error'); } } return; }
Now after this, when session variable was called on any other page. it was throwing error. Most of which i handled using isset() but in the situation mentioned in my first post i don't know what to do.
-
On 7/16/2020 at 6:23 PM, gw1500se said:
Is there a session_start call at the beginning of this script?
Yes, it is called on top of everything.
<?php // Start Session session_start(); // Include Config require('config.php'); // db config require('classes/Messages.php'); // error or success messages require('classes/Bootstrap.php'); // handling controllers, actions and requests according to URLs require('classes/Controller.php'); // handling includes require('classes/Model.php'); // *this one contains functions related to queries. require('controllers/home.php'); require('controllers/shares.php'); require('controllers/users.php'); // in this file the session variable was set, when user submits username (no password is required) // Using * in query is for testing purpose only require('models/home.php'); require('models/share.php'); require('models/user.php'); $bootstrap = new Bootstrap($_GET); $controller = $bootstrap->createController(); if($controller){ $controller->executeAction(); }
Below is the code from model.php
<?php abstract class Model{ protected $dbh; protected $stmt; public function __construct(){ $this->dbh = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME, DB_USER, DB_PASS); } public function query($query){ $this->stmt = $this->dbh->prepare($query); } //Binds the prep statement public function bind($param, $value, $type = null){ if (is_null($type)) { switch (true) { case is_int($value): $type = PDO::PARAM_INT; break; case is_bool($value): $type = PDO::PARAM_BOOL; break; case is_null($value): $type = PDO::PARAM_NULL; break; default: $type = PDO::PARAM_STR; } } $this->stmt->bindValue($param, $value, $type); } public function execute(){ $this->stmt->execute(); } public function resultSet(){ $this->execute(); return $this->stmt->fetchAll(PDO::FETCH_ASSOC); } public function lastInsertId(){ return $this->dbh->lastInsertId(); } public function single(){ $this->execute(); return $this->stmt->fetch(PDO::FETCH_ASSOC); } }
One more thing that session variable was giving multiple errors on live server when I used it from mobile. i handled most of them using isset() but i don't know how to handle the one that i mentioned in my first post. Please guide.
-
I am using the following code in PDO query
$this->query('SELECT * FROM users WHERE id = :id'); // line 11 $this->bind(':id', $_SESSION['users']['id']); // line 12
it is working on local server, but on live server its giving some error or warning (i am not sure)
PHP Notice: Undefined index: users in ... on line 12
but the query is still working correctly.
Please guide me what should i do to stop getting this error or warning or notice. Thanks🙂
Splitting multidimensional array based on values
in PHP Coding Help
Posted
Thank you sir for guiding me. The result i want from query is like...
+---------+-----------+----------+ | user_id | status_id | username | +---------+-----------+----------+ | 14 | 1 | user14 | | 13 | 1 | user13 | | 8 | 1 | user8 | | 7 | 1 | user7 | | 6 | 1 | user6 | | 12 | 2 | user12 | | 11 | 2 | user11 | +---------+-----------+----------+I had to modify my table. added more columns now. so i need to get values in separate columns instead of rows