Barand
-
Posts
24,566 -
Joined
-
Last visited
-
Days Won
822
Everything posted by Barand
-
OK. I wanted to know if I was on the right track. SELECT pd.enterprise, COUNT(*) AS total FROM production_data pd LEFT JOIN production_status ps ON ps.order_id = pd.id AND ps.dept_code = 13 AND ps.status_id = 3 WHERE pd.enterprise = "EXXON" AND pd.as400_ship_date = '2021-03-02' AND pd.hold_date = "0000-00-00" AND ps.order_id IS NULL;
-
The query returns SELECT pd.enterprise, COUNT(*) AS total The enterprise columns are all EXXON but what should the count be? I get 31 as there are 32 records but 1 has a status where its dept_code is = 13 and it's status_id = 3, therefore excluded. Is that what you expect?
-
From that data, what are the expected results? So I know if I get it right.
-
Your query selects on as400_ship_date and hold_date. That production_data contains no date data therefore no records will be selected.
-
Are you sure? Last time I used MariaDB I had to use a LIMIT clause (with a huge number (264 - 1) to force it to write a subquery to a temp table, otherwise it wouldn't accept an ORDER BY clause. E.G. ORDER BY id LIMIT 18446744073709551615 Your query will be more efficent if you use a JOIN rather than the "NOT IN subquery". If you post a dump with test data I'll have go at rewriting it for you.
-
This would be the fastest way $local = PDO connection to database on local db server $remote = PDO connection to database on remote server // // GET DATA FROM LOCAL SERVER // $res = $local->query("SELECT user_id, user_username FROM users"); $users = []; foreach ($res as $row) { $users[] = vsprintf("(%d, '%s')", $row); } // // WRITE DATA TO REMOTE SERVER // 1,000 RECORDS AT A TIME // $chunks = array_chunk($users, 1000); $count = 0; foreach ($chunks as $data) { $count += $remote->exec("INSERT INTO users (id, username) VALUES " . join(',', $data)); } echo "$count records transferred";
-
We need to change the alias of the date column to something other than "date" otherewise it willl try to sort using the d/m/Y format, which doesn't work. We need to sort by the original date (Y-m-d) Therefore the query will become SELECT name , comment , DATE_FORMAT(date, '%d/%m/%Y') as formatted FROM image ORDER BY date DESC; (Don't forget to use your column names and where you had $row['date'] you now need $row['formatted'])
-
How to display every photo from a database? [closed]
Barand replied to veks21's topic in PHP Coding Help
Where do create the instances of your database and photo_display classes? How are you storing the images. <img src needs to be a filename (or encoded data), not var_dump output. -
Here's an example of the javascript pprocessing. (I don't know what your tables look like to give you an example of the update.) <!DOCTYPE html> <html> <head> <title>Example</title> <meta http-equiv="content-language" content="en"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <!-- link to jquery functions --> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script type='text/javascript'> $().ready( function() { $("#approved").change( function() { if ($(this).val() > '') $("#status").val("Approved") else $("#status").val("Pending") }) }) </script> </head> <body> Status<br> <input type='text' name='status' id='status' value='Pending'> <br><br> Date approved<br> <input type='date' name='approved' id='approved'> </body> </html>
-
Then how are they related to the update query?
-
When the admin adds an approval date, update the date and status in your tables when saved. If you want immediately to alter the status display on screen, use a javascript onchange event on the date input to change the status to Approved.
-
This is the same problem as your post in the php coding forum - don't double post.
-
The errors you posted refer to lines 10 and 14. Which ones are they in your code?
-
Don't allow users to enter dates in any format - use a datepicker or HTML5 date input to enforce uniformity of input, and always store in yyyy-mm-dd format. Luckily you only appear to have only three formats (Y-m-d / d M Y / d-m-Y) so all is not lost. TEST DATA mysql> select * from date_example; +----+-------------+----------+ | id | date | time | +----+-------------+----------+ | 1 | 2005-05-21 | 14:00:00 | | 2 | 22 Jun 2006 | 13:00:00 | | 3 | 01-11-2009 | 12:00:00 | | 4 | 22-03-2021 | 00:05:00 | | 5 | 28 Mar 2021 | 08:00:00 | +----+-------------+----------+ QUERY SELECT * FROM ( SELECT id , date , CASE WHEN str_to_date(date, '%d %b %Y') IS NOT NULL -- eg 01 Jan 2021 THEN str_to_date(date, '%d %b %Y') WHEN str_to_date(date, '%d-%m-%Y') IS NOT NULL -- eg 10-01-2021 THEN str_to_date(date, '%d-%m-%Y') ELSE date END as newdate , time FROM date_example ) converted WHERE concat(newdate, ' ', time) < NOW() - INTERVAL 36 HOUR; RESULTS +----+-------------+------------+----------+ | id | date | newdate | time | +----+-------------+------------+----------+ | 1 | 2005-05-21 | 2005-05-21 | 14:00:00 | | 2 | 22 Jun 2006 | 2006-06-22 | 13:00:00 | | 3 | 01-11-2009 | 2009-11-01 | 12:00:00 | | 4 | 22-03-2021 | 2021-03-22 | 00:05:00 | +----+-------------+------------+----------+
-
Are you talking about HTML tables on the screen or database tables?
-
Are you saying you have a single (varchar) column with dates in different formats, or multiple date columns in the table? A sample of the data would help. Easiest way would be to store the time and the date in a single DATETIME column, then search for "WHERE mydatetime < NOW() - INTERVAL 36 HOUR". You need to convert dates that are not in yyyy-mm-dd format to that format.
-
Sounds like $_GET['p_id'] doesn't exist so $the_user_id doesn't get defined. Stop putting user data directly into the query - use prepared queries.
-
That error means your query returned false (failed) instead of a valid result. My guess would be that the column names in my example are not the same as those in your table.
-
You need to call session_start() before any out put is sent to the browser. (Top of the script is best place). In check_login you hava an echo before the session_start().
-
If we just get the data straight from the table we get SELECT name , comment , date FROM date_example; +-------+--------------------------------------------------------+------------+ | name | comment | date | +-------+--------------------------------------------------------+------------+ | Peter | Lorem ipsum dolor sit amet | 2005-05-21 | | Paul | Nunc viverra imperdiet enim | 2005-06-22 | | Curly | Suspendisse dui purus, scelerisque at, vulputate vitae | 2006-11-01 | | Larry | Etiam eget dui. Aliquam erat volutpat. | 2006-11-02 | | Mo | Etiam at ligula et tellus ullamcorper ultrices. | 2019-09-28 | +-------+--------------------------------------------------------+------------+ However, formatting the date as I showed gives SELECT name , comment , DATE_FORMAT(date, '%d/%m/%Y') as date FROM date_example; +-------+--------------------------------------------------------+------------+ | name | comment | date | +-------+--------------------------------------------------------+------------+ | Peter | Lorem ipsum dolor sit amet | 21/05/2005 | | Paul | Nunc viverra imperdiet enim | 22/06/2005 | | Curly | Suspendisse dui purus, scelerisque at, vulputate vitae | 01/11/2006 | | Larry | Etiam eget dui. Aliquam erat volutpat. | 02/11/2006 | | Mo | Etiam at ligula et tellus ullamcorper ultrices. | 28/09/2019 | +-------+--------------------------------------------------------+------------+ So exactly what does "not working" mean? PS How are you currently storing your dates? It should be TYPE DATE (or DATETIME) with a format of YYYY-MM-DD
-
True by default when no keys specified. $letters = ['a', 'b', 'c']; echo $letters[0]; //--> 'a' echo $letters[1]; //--> 'b' echo $letters[2]; //--> 'c' // but $months = [ 1 => 'Jan', 'Feb', 'Mar' ]; echo $months[0]; //-> Notice: Undefined offset: 0 echo $months[1]; //-> 'Jan' echo $months[2]; //-> 'Feb'
-
Look at the data in the mail headers. For example, an email from a friend of mine in Kentucky (timezone EST) had this in the header Date: Sun, 28 Feb 2021 16:13:50 -0500 ^^^^^ ||||| GMT - 5 hrs
-
Stop using "SELECT *" and specify the fields you need. Use mysql DATE_FORMAT() give the required format, or format it in php with date() function SELECT name , comment , DATE_FORMAT(date, '%d/%m/%Y') as date FROM tablename
-
Updating Textbox2 based on Textbox1 Autocomplete
Barand replied to Moorcam's topic in Javascript Help
This appears to work script <script type='text/javascript'> $(function() { $( "#location" ).autocomplete({ source: 'autosearch.php', select: function(event, ui) { event.preventDefault() $("#location").val(ui.item.label) $("#location_phone").val(ui.item.value) } }); }); </script> autosearch.php if (isset($_GET['term'])) { $res = $conn->prepare("SELECT concat(user_firstname,' ',user_lastname) as label , user_phone as value FROM users WHERE concat(user_firstname,' ',user_lastname) LIKE ? "); $res->execute([ $_GET['term'].'%' ]); $data = $res->fetchAll(PDO::FETCH_OBJ); exit(json_encode($data)); } -
Try this. It doesn't appear to impact performance. Unmodified : 2987 rows in set (1.53 sec) Modified : 2987 rows in set (1.48 sec) I had to use GROUP BY to get a single row with 1234 1 1 otherwise, if both matched, it gave 1234 1 0 1234 0 1 Query SELECT id , SUM(match_email) as by_email , SUM(match_phone) as by_phone FROM ( SELECT `a1`.`id`, 1 as match_email, 0 as match_phone FROM ( ( `customers` `a1` JOIN `customers` `b1` ON ( ( (`a1`.`email` = `b1`.`email`) AND(`a1`.`id` <> `b1`.`id`) AND( `a1`.`email` <> '[email protected]' ) AND( `b1`.`email` <> '[email protected]' ) ) ) ) JOIN `quotes` `q1` ON ( ( (`q1`.`customer_id` = `b1`.`id`) AND(`q1`.`purchased` = 1) ) ) ) UNION SELECT `a`.`id`, 0 as match_email, 1 as match_phone FROM ( ( `customers` `a` JOIN `customers` `b` ON ( ( (`a`.`phone` = `b`.`phone`) AND(`a`.`id` <> `b`.`id`) ) ) ) JOIN `quotes` `q` ON ( ( (`q`.`customer_id` = `b`.`id`) AND(`q`.`purchased` = 1) ) ) ) ) all_matches GROUP BY `id`
