-
Posts
24,563 -
Joined
-
Last visited
-
Days Won
822
Everything posted by Barand
-
Duplicating Mysql Table rows but also updating some content
Barand replied to Adamhumbug's topic in PHP Coding Help
Start with SELECT q.id , q.version FROM quote q JOIN ( SELECT client_id , max(version) as version FROM quote WHERE client_id = 15 ) latest USING (client_id, version); +----+---------+ | id | version | +----+---------+ | 71 | 6 | +----+---------+ Now you know that for client #15 the latest quote has id = 71 and its version is 6. Duplicate quote #71, giving the dupe a verion of 7, and duplicate the items for #71 with the quote_id of the new duped quote. -
You beat me to it.
-
You can't embed a function call in a string. You need to concatenate EG echo "Today is " . date('l'); not echo "Today is date('l')";
-
When you view the page source, what have you got where you expect the options of the select?
-
It's useful if you give us something we can test with. The less time we have to spend on your problem, the more chance there is of a good response. We aren't paid by the hour! Ideally, a data export dump of the relevant tables with test data so it can be reloaded at our end and the query tested. That way we can see your results and problem first hand. As with code, pictures of data are as much use as a chocolate teapot.
-
Do you have any data with j.id = 15? There aren't any in the useless pictures of your data.
-
Do not double-post topics. Post in the correct forum (Moving this from Introductions to Javascript forum)
-
Everything echo'd in response to a fetch or ajax request goes into the response that is returned to your handler function. They do not go to the screen for you to read. Use your browser developer tools network tab to view the response.
-
Something like this? ... SELECT j.id as job_id , j.name , count(q.id) as quotes FROM job j LEFT JOIN quote q ON j.id = q.job_id GROUP BY j.id
-
Ajax appended table colour change based on the conditon
Barand replied to Senthilkumar's topic in Javascript Help
What don't you understand? I commented it for you. -
Ajax appended table colour change based on the conditon
Barand replied to Senthilkumar's topic in Javascript Help
Something like this, perhaps. I used just the numeric values from your JSON data. No point processsing everything twice. <?php ################################################################################ ## PROCESS AJAX REQUEST ################################################################################ if (isset($_GET['ajax'])) { $data = array ( 0 => array ( 'Month' => 'Feb-2023', 'TIV' => '0', 'Marketingpromotion' => '0', 'Revenue' => '1', 'Equipementpopulation' => '1', 'Employeeexpense' => '0', 'Infrastructureexpense' => '0', 'Otherexpense' => '0', ), 1 => array ( 'Month' => 'Apr-2023', 'TIV' => '1', 'Marketingpromotion' => '1', 'Revenue' => '1', 'Equipementpopulation' => '1', 'Employeeexpense' => '1', 'Infrastructureexpense' => '1', 'Otherexpense' => '1', ), 2 => array ( 'Month' => 'May-2023', 'TIV' => '2', 'Marketingpromotion' => '1', 'Revenue' => '0', 'Equipementpopulation' => '0', 'Employeeexpense' => '0', 'Infrastructureexpense' => '0', 'Otherexpense' => '0', ), 3 => array ( 'Month' => 'Jun-2023', 'TIV' => '3', 'Marketingpromotion' => '1', 'Revenue' => '0', 'Equipementpopulation' => '0', 'Employeeexpense' => '1', 'Infrastructureexpense' => '0', 'Otherexpense' => '0', ) ); exit (json_encode($data)); } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset='utf-8'> <title>Example</title> <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script> <script type='text/javascript'> var texts = [ "Pending from Dealer", "Pending from 1st Approver", "Pending from 2nd Approver", "Approval Complete" ]; var classes = [ "pending", "progress", "progress", "submitted" ]; $(function() { $("#btndata").click( function() { $.get( "", // sends request to self {"ajax":1}, function(resp) { $("#tdata").empty() $.each(resp, function(i, rec) { let sno = i+1 let newrow = $("<tr>") // create new row let cell = $("<td>", {"html":sno}) // add Sno col $(newrow).append(cell) // process records $.each(rec, function(k, v) { if (k=="Month") { cell = $("<td>", {"html":v}) $(newrow).append(cell) // add month } else { newrow.append(statusCell(v)) // get content and class from cell value - add to row } }) $("#tdata").append(newrow) // add the row to the table body }) }, "JSON" ) }) }) function statusCell(v) { let cls = classes[v] let txt = texts[v] return $("<td>", {"class":cls, "html":txt}) } </script> <style type='text/css'> header { padding: 16px 0; margin-bottom: 20px;; } table { width: 100%; border-collapse: collapse; margin: 20px 0; } th, td { padding: 4px; text-align: center; } th { background-color: black; color: white; } .pending { color: red; } .progress { color: orange; } .submitted { color: green; } </style> </head> <body> <header> <h1>Example</h1> </header> <button id='btndata'>Get data</button> <table border='1'> <thead> <tr><th>S.No</th> <th>Date</th> <th>TIV</th> <th>Marketing<br>Promotion</th> <th>Revenue</th> <th>Equipment<br>Population</th> <th>Employee<br>Expense</th> <th>Infrastructure<br>Expense</th> <th>Other<br>Expense</th> </tr> </thead> <tbody id='tdata'> <!-- data goes here --> </tbody> </table> </body> </html> output -
Ajax appended table colour change based on the conditon
Barand replied to Senthilkumar's topic in Javascript Help
Patience, Grasshopper. I'm working on it. -
Ajax appended table colour change based on the conditon
Barand replied to Senthilkumar's topic in Javascript Help
Can you post an example of the JSON response string that you are processing? -
CSV export open CSV in the browser instead of download file
Barand replied to MushMX's topic in PHP Coding Help
Have you tried adding a "download" attribute to the <a> element? eg <a href="filename" download >Download fiename</a> -
CSV export open CSV in the browser instead of download file
Barand replied to MushMX's topic in PHP Coding Help
The code I gave you in your previous topic downloads the file. -
-- first UPDATE thetable SET display_order = display_order + 1 WHERE display_order >= 2; -- then INSERT INTO thetable (item_name, display_order) VALUES ('Item 4', 2);
-
You do have the $items array available to that second foreach(). Just access the value you need.
-
I too don't know what your esoteric functions are returning but here is the code I have been using for years to produce data for my christmas card address labels $db = new mysqli(HOST,USERNAME,PASSWORD,'mydb'); $y = date("Y"); $filename = "xmaslist_$y.csv"; $sql = "SELECT family_vw.addressee , add1 , add2 , town , county , postcode , CASE WHEN xmas.post=1 THEN 'Y' ELSE '' END as post , xmas.cardcategory FROM xmas INNER JOIN family_vw USING (family_id) ORDER BY xmas.cardcategory "; sql2csv($db, $sql, $filename,); /** * Write query output to csv file for export * * Parameters * $conn - database connection * $sql - the sql query to be executed * $filename - name of download file (default "download_yymmddhhii.csv") * $headings - 1 if fieldname headings required (default), 0 if not required */ function sql2csv($conn, $sql, $filename='', $headings=1) { if (!$filename) $f = 'download_' . date('ymdhi') . '.csv'; else $f = $filename; $fp = fopen('php://output', 'w'); // so you can fputcsv to STDOUT if ($fp) { $res = $conn->query($sql); if ($res) { header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="'.$f.'"'); header('Pragma: no-cache'); header('Expires: 0'); $row = $res->fetch_assoc(); if ($headings) { fputcsv($fp, array_keys($row)); } do { fputcsv($fp, $row); } while ($row = $res->fetch_assoc()); } else echo "Error in query"; fclose($fp); } }
-
Oops! FETCH_GROUP should be FETCH_ASSOC. You also need to change the order of your selected columns so that the first two are the ones you are using as the keys, then the array_slice takes the rest..
-
You don't want to help - too much to ask? OK - Here's how it looks with my data... +-----------+-----------+------------+-----------+-------------------+------------+ | member_id | user_name | first_name | last_name | email | phone | +-----------+-----------+------------+-----------+-------------------+------------+ | 1 | cheggs | Scott | Chegg | cheggs@ggmail.com | 1345678902 | | 2 | norderl | Laura | Norder | lauran@ggmail.com | 2345678901 | | 3 | canarit | Tom | DiCanari | tomdc@ggmail.com | 6543219878 | | 4 | peted | Peter | Dowt | pete.d@ggmail.com | 9876543210 | | 5 | tonins | Sarah | Tonin | saraht@ggmail.com | 7896321455 | +-----------+-----------+------------+-----------+-------------------+------------+ code $res = $pdo->query("SELECT user_name , last_name , first_name , email , phone FROM member "); $data = $res->fetchAll(PDO::FETCH_GROUP); $result = []; foreach ($data as $u) { $result[$u['user_name']][$u['last_name']] = array_slice($u,2); } echo '<pre>' . print_r($result, 1) . '</pre>'; results Array ( [cheggs] => Array ( [Chegg] => Array ( [first_name] => Scott [email] => cheggs@ggmail.com [phone] => 1345678902 ) ) [norderl] => Array ( [Norder] => Array ( [first_name] => Laura [email] => lauran@ggmail.com [phone] => 2345678901 ) ) [canarit] => Array ( [DiCanari] => Array ( [first_name] => Tom [email] => tomdc@ggmail.com [phone] => 6543219878 ) ) [peted] => Array ( [Dowt] => Array ( [first_name] => Peter [email] => pete.d@ggmail.com [phone] => 9876543210 ) ) [tonins] => Array ( [Tonin] => Array ( [first_name] => Sarah [email] => saraht@ggmail.com [phone] => 7896321455 ) ) )
-
Thanks, but pictures are real bastard to process. a var_export($array) with a few records would be much more useful.
-
I'll try and find a similar table of my own to test with and try again
-
try $res = $pdo->query("SELECT client.company_name , job.name as jobName , version , currency , job.internal_ref , kit_delivery , kit_return , quote_status_id from quote inner join client on quote.client_id = client.id inner join job on quote.job_id = job.id "); $data = $res->fetchAll(PDO::FETCH_GROUP); $result = [ 'Test_Co' => $data ]; echo '<pre>' . print_r($result, 1) . '</pre>';
-
One more point, do not connect to your db server every time you call a function. Connecting is the slowest part of the process. Connect once at the start of the script and pass $pdo as an argument in the function calls (as with the other arguments that @ginerjm suggested).