Barand
-
Posts
24,335 -
Joined
-
Last visited
-
Days Won
795
Community Answers
-
Barand's post in Variable mySQLi prepared statement was marked as the answer
Using your current method (but with arrays and PDO) it becomes
$databases = [ 'database1', 'database2', 'database3', 'database4', 'database5' ]; $baseSQL = "SELECT FirstName, LastName, Email, Mobile, PromoCode FROM <DB>.tablename WHERE (Email LIKE ?) OR (Mobile LIKE ?) OR (PromoCode LIKE ?) "; $queries = []; $params = []; $srch = isset($_GET['search']) ? '%' . $_GET['search'] . '%' : '%'; foreach ($databases as $dbname) { $queries[] = str_replace('<DB>', $dbname, $baseSQL ); array_push($params, $srch, $srch, $srch); } $sql = join("\nUNION\n", $queries); // build set of UNION queries /********** DEBUG ONLY _ DISPLAY QUERY **************/ echo '<pre>', $sql, '</pre>'; /****************************************************/ $stmt = $db->prepare($sql); $stmt->execute($params); -
Barand's post in Is this possible to do with php and mysql? was marked as the answer
My 0.02 worth
Tables
CREATE TABLE `users` ( `user_id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT, `username` varchar(45) DEFAULT NULL, `password` varchar(150) DEFAULT NULL, `email` varchar(150) DEFAULT NULL, PRIMARY KEY (`user_id`) ); CREATE TABLE `matrix` ( `matrix_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `sponsor_id` int(11) DEFAULT NULL, PRIMARY KEY (`matrix_id`), KEY `idx_matrix_user_id` (`user_id`), KEY `idx_matrix_sponsor_id` (`sponsor_id`) ); -- seed matrix with at least 1 record INSERT INTO matrix(user_id, sponsor_id) VALUES (null,1); This sample form adds a new user then inserts the user into the matrix table. If a sponsor is specified then that sponsors id is inserted, otherwise it is added to the first available sponsor (ie with less than 14 users)
Just curious - why 14?
<?php // Your PDO connection code goes here if ($_SERVER['REQUEST_METHOD']=='POST') { // NOTE : validation omitted for brevity $sql = "INSERT INTO users (username,password,email) VALUES (:user,:pass,:email)"; $stmt = $db->prepare($sql); $stmt->execute( [ 'user' => $_POST['name'], 'pass' => password_hash($_POST['pwd'], PASSWORD_DEFAULT), 'email' => $_POST['email'] ]); // get the id of the newly added user $userid = $db->lastInsertId(); // add new user into the matrix $sql = "INSERT INTO matrix (user_id, sponsor_id) SELECT @user as user , CASE WHEN @sponsor=0 THEN m.sponsor_id ELSE @sponsor END as sponsor FROM ( SELECT sponsor_id , COUNT(user_id) as tot FROM matrix WHERE sponsor_id IS NOT NULL GROUP BY sponsor_id HAVING tot < 14 ORDER BY sponsor_id LIMIT 1 ) m JOIN (SELECT @user := :user, @sponsor := :sponsor) init"; $stmt = $db->prepare($sql); $stmt->execute([ ':user' => $userid, ':sponsor' => $_POST['sponsor'] ]); } function userOptions($db, $current=0) { $sql = "SELECT user_id , username FROM users ORDER BY username"; $res = $db->query($sql); $opts = '<option value="0">--No sponsor specified--</option>'; foreach ($res as $r) { $opts .= "<option value='$r[user_id]'>$r[username]</option>\n"; } return $opts; } function currentUsers($db) { $sql = "SELECT u1.user_id , u1.username , GROUP_CONCAT(u2.user_id ORDER BY u2.user_id SEPARATOR ' | ') as users FROM matrix m INNER JOIN users u1 ON m.sponsor_id = u1.user_id INNER JOIN users u2 ON m.user_id = u2.user_id GROUP BY u1.user_id ORDER BY u1.user_id"; $res = $db->query($sql); $usrs = ''; foreach ($res as $r) { $usrs .= "<tr><td>$r[user_id]</td><td>$r[username]</td><td>$r[users]</td></tr>\n"; } return $usrs; } ?> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>codeword_entry</title> <meta name="author" content="Barry Andrew"> <style type="text/css"> body { font-family: sans-serif; font-size: 10pt; } fieldset { padding: 15pt; background-color: #ccc; } legend { background-color: black; color: white; padding: 3px; } table { font-family: sans-serif; font-size: 10pt; border-spacing: 1px; min-width: 700px; } th { background-color: black; color: white; padding: 5px 3px; } td { padding: 3px; } </style> </head> <body> <h1>Add New User</h1> <form action="" method='POST'> <fieldset> <legend>User details</legend> User name <input type="text" name="name" size="40"><br> Password <input type="password" name="pwd" size="40"><br> Email <input type="text" name="email" size="50"> </fieldset> <fieldset> <legend>Sponsor</legend> Sponsor <select name="sponsor"><?=userOptions($db)?></select> </fieldset> <input type="submit" name="btnSub" value="Submit"> </form> <hr> <h2>Current Users</h2> <table> <tr><th>ID</th><th>User name</th><th>Sponsored Users</th></tr> <?=currentUsers($db)?> </table> </body> </html> -
Barand's post in if row exists update else insert was marked as the answer
Set a UNIQUE index on phone. Tablets can all have null values.
EG
CREATE TABLE `customers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fullName` varchar(50) DEFAULT NULL, `phone` varchar(45) DEFAULT NULL, `pin` int(11) DEFAULT NULL, `device` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_customers_phone` (`phone`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8; mysql> select * from customers; +----+----------+----------+------+--------+ | id | fullName | phone | pin | device | +----+----------+----------+------+--------+ | 1 | Cust_1 | 12346789 | 1234 | phone | | 2 | Cust_2 | 12356789 | 3456 | phone | | 3 | Cust_3 | 12366789 | 5678 | phone | | 4 | Cust_4 | NULL | NULL | tablet | | 5 | Cust_5 | NULL | NULL | tablet | +----+----------+----------+------+--------+ INSERT INTO customers (fullname,phone,pin,device) VALUES ('Cust_6', 12346789, 1010, 'phone') ON DUPLICATE KEY UPDATE pin = 1010; mysql> select * from customers; +----+----------+----------+------+--------+ | id | fullName | phone | pin | device | +----+----------+----------+------+--------+ | 1 | Cust_1 | 12346789 | 1010 | phone | <-- updated | 2 | Cust_2 | 12356789 | 3456 | phone | | 3 | Cust_3 | 12366789 | 5678 | phone | | 4 | Cust_4 | NULL | NULL | tablet | | 5 | Cust_5 | NULL | NULL | tablet | +----+----------+----------+------+--------+ -
Barand's post in Best guess date from messy string. was marked as the answer
Provided that you can spell "February", only two of those formats do not work
$dates = [ 'feb 21, 1999', 'February 21, 1999', '02/21/99', '2/21/99', '99/2/21', '2-21-1999', '19990221', 'sun, Feb 21, 1999', 'Sunday February 21, 1999' ]; echo '<pre>'; foreach ($dates as $dstr) { printf("%-30s%-15s%s\n", $dstr, date('Y-m-d', strtotime($dstr)), strtotime($dstr)==0 ? 'X' : '' ); } Outputs
feb 21, 1999 1999-02-21 February 21, 1999 1999-02-21 02/21/99 1999-02-21 2/21/99 1999-02-21 99/2/21 1970-01-01 X 2-21-1999 1970-01-01 X 19990221 1999-02-21 sun, Feb 21, 1999 1999-02-21 Sunday February 21, 1999 1999-02-21 For rogue formats you can always use DateTime::createFromFormat() -
Barand's post in mySQLi statement for multiple databases was marked as the answer
Prefix the table name with the database name
$sql = "SELECT ID, FirstName, Last Name, Email, Mobile, PromoCode FROM database1.table1 WHERE (Email LIKE ?) OR (Mobile LIKE ?) OR (PromoCode LIKE ?) UNION SELECT ID, FirstName, Last Name, Email, Mobile, PromoCode FROM database2.table1 WHERE (Email LIKE ?) OR (Mobile LIKE ?) OR (PromoCode LIKE ?) "; Databases must be on the same server.
-
Barand's post in Hello A-Z Help was marked as the answer
try
foreach (range('A','Z') as $alpha) { echo "<li data-text='$alpha'>$alpha</li>\n"; } -
Barand's post in Sort TEXT values as INT was marked as the answer
Looks like the "space" is some other whitespace character.
What does
SELECT HEX(price) as hexprice; give you?
-
Barand's post in Coding a nested serialized PHP array was marked as the answer
Yes - it has been serialized twice.
Serialize the array into a string
Serialize the resulting string
To unravel it, unserialize twice
print_r( unserialize(unserialize('s:287:"a:8:{s:5:"price";a:2:{s:5:"value";s:5:"38000";s:8:"original";s:0:"";}s:17:"custom_tax_inside";s:0:"";s:15:"custom_tax_page";s:0:"";s:8:"city_mpg";a:1:{s:5:"value";s:0:"";}s:11:"highway_mpg";a:1:{s:5:"value";s:0:"";}s:12:"custom_badge";s:0:"";s:5:"video";s:0:"";s:10:"short_desc";s:0:"";}"'))); Gives
Array ( [price] => Array ( [value] => 38000 [original] => ) [custom_tax_inside] => [custom_tax_page] => [city_mpg] => Array ( [value] => ) [highway_mpg] => Array ( [value] => ) [custom_badge] => [video] => [short_desc] => ) -
Barand's post in select between rate range at set time. was marked as the answer
So for it to work as it did with a separate time column, are the time elements the same in the two datetime columns?
When comparing a date with a datetime column you need to use only the date portion of the datetime.
So if my opening assumption is true
SELECT id , schedule_start , schedule_end WHERE UTC_DATE() BETWEEN DATE(schedule_start) AND DATE(schedule_end) AND EXTRACT(HOUR_MINUTE FROM UTC_TIME()) = EXTRACT(HOUR_MINUTE FROM schedule_start)
-
Barand's post in Populate array from another array was marked as the answer
Change
'options' => array_values($category_str) to
'options' => $category_str; -
Barand's post in Myphpadmin - DD MM YYYY ? was marked as the answer
You could try reading thara's post again and use the function he gave you.
edit - alternatively you can do it in PHP
$dtobj = new DateTime($row['date']); echo $dtobj->format('d/m/Y'); //--> 23/01/2017, for example -
Barand's post in Database Diagram was marked as the answer
The table you needed to add is the "reserva_hora" to give a many-to-many relationship between reserva and hora.
-
Barand's post in pdo count where was marked as the answer
Syntax error in the query. Remove the comma after "as total".
As COUNT(*) now has the column alias "total" you should refer to it as $row['total'] and not $row['COUNT(*)'];
It's easier to give expressions like that a column alias, especially for complex expressions.
-
Barand's post in PHP Search results - Side by side rather than stacked was marked as the answer
Easiest way is to put the results in divs with float:left and width ~= 25%
-
Barand's post in How to remove comma from last row in mysql query in php was marked as the answer
Put into an array then join()
$Q = "SELECT length, width FROM statxyx WHERE siteid='$siteid'"; $R = mysqli_query($DB,$Q); //start loop //while or foreach $results=[]; // define array while($row = mysqli_fetch_assoc($R)){ $results[] = "['7C6Buh',{$row['length']},{$row['width']}]"; // add to array } $final = join(",\r\n", $results); -
Barand's post in ranking based on score and time elapsed was marked as the answer
test data
insert into ranking (id_jogo, id_user, pontuacao, data, tempo) VALUES (5141, 11 , 15, '2016-12-27', '00:00:03'), (7001 , 9 , 10, '2016-12-27', '00:00:06'), (2519 , 7 , 5, '2016-12-27', '00:00:07'), (4585 , 6 , 15, '2016-12-27', '00:00:04'), (4585 , 5 , 10, '2016-12-27', '00:00:05'), (4585 , 4 , 10, '2016-12-27', '00:00:07'), (4585 , 3 , 6, '2016-12-27', '00:00:07'), (4585 , 2 , 7, '2016-12-27', '00:00:07'), (4585 , 1 , 10, '2016-12-27', '00:00:06'); query
SELECT id_user , pontuacao , data , tempo , rank FROM ( SELECT id_user , data , @row := @row+1 as row , @rank := IF(@prevpont = pontuacao AND @prevtemp = tempo, @rank, @row) as rank , @prevpont := pontuacao as pontuacao , @prevtemp := tempo as tempo FROM ranking JOIN (SELECT @prevtemp:='0:00:00', @prevpont:=0, @row:=0, @rank:=0) init ORDER BY pontuacao DESC, tempo ) calc ; results
+---------+-----------+------------+----------+--------+ | id_user | pontuacao | data | tempo | rank | +---------+-----------+------------+----------+--------+ | 11 | 15 | 2016-12-27 | 00:00:03 | 1 | | 6 | 15 | 2016-12-27 | 00:00:04 | 2 | | 5 | 10 | 2016-12-27 | 00:00:05 | 3 | | 9 | 10 | 2016-12-27 | 00:00:06 | 4 | = | 1 | 10 | 2016-12-27 | 00:00:06 | 4 | = | 4 | 10 | 2016-12-27 | 00:00:07 | 6 | | 2 | 7 | 2016-12-27 | 00:00:07 | 7 | | 3 | 6 | 2016-12-27 | 00:00:07 | 8 | | 7 | 5 | 2016-12-27 | 00:00:07 | 9 | +---------+-----------+------------+----------+--------+ -
Barand's post in How to filter Select option to ALL and Show Data Based on ALL Select Option was marked as the answer
Spot the difference?
-
Barand's post in Sum row in UNION was marked as the answer
For a start, too many "UNIONS".
SELECT MONTH(due_date) as month , SUM(amount_paid) as total FROM ( SELECT due_date, amount_paid FROM table1 UNION ALL SELECT due_date, amount_paid FROM table2 )x GROUP BY month If you are having to do that, it looks like your table1 and table2 should be a single table with an additional identifier column.
-
Barand's post in Query multiple databases in the same query was marked as the answer
Yes, that's OK. So long as they are both on the same server. The connection is to the server. And, as you said, you have privileges to access all databases in the query.
-
Barand's post in user defined functions in separate file or not was marked as the answer
Horses for courses. It depends on where you will use the function. If it is specific to one page, define it in that page. If it is used by more than one page then define it in a separate file.
So some will be defined in the page, some in an application-specific function file and some in general function file.
-
Barand's post in retrieve data from mysql using a search field in wordpress was marked as the answer
The content data is JSON encoded, so you need to decode it then access the content fields
if (isset($_GET['zoeknummer'])) { $sql = "SELECT lead_content FROM lead"; $res = $pdo->query($sql); // where $pdo is your db connection while ($lead = $res->fetchColumn()) { $data = json_decode($lead); if ($data->zoeknummer == $_GET['zoeknummer']) { echo $data->komplex . ' : ' . $data->plaats . '<br>'; } } } -
Barand's post in stratotime inbetween dates was marked as the answer
try
$timestamp = $output2['data'][$acc_id]['last_battle_time']; $dt1 = new DateTime(); $dt1->setTimestamp($timestamp); $dt2 = new DateTime(); echo ceil($dt1->diff($dt2)->days / 7) * 7; -
Barand's post in how to change the date formate in excel from 01.01.2017 to 01.01.17 was marked as the answer
Select date cells and right click
Select "format cells"
Choose "custom"
enter "mm.dd.yy"
-
Barand's post in Foreach array (sum array + condition) was marked as the answer
This uses the above model to produce your table output
<?php // PDO connection $db = pdoConnect('foo'); $sql = "SELECT course,certlevel FROM course ORDER BY certlevel,course_id"; $res = $db->query($sql); $courses = []; foreach ($res as $crs) { $courses[ $crs['certlevel'] ][] = $crs['course']; } // build table heading and empty table row array $thead = "<tr><th>Name</th>"; $newarray = []; foreach ($courses as $cert=>$crss) { $cclass = "L$cert"; $thead .= "<th class='$cclass'>" . join("</th><th class='$cclass'>",$crss) . "</th><th class='$cclass'>Certificate $cert</th>"; foreach ($crss as $cname) { $newarray[$cert][$cname] = 0; } } $thead .= "</tr>\n"; // get the student marks $sql = "SELECT s.name , c.course , c.certlevel , e.marks FROM student s LEFT JOIN enrolment e USING (student_id) LEFT JOIN course c USING (course_id) ORDER BY e.student_id, c.certlevel, e.course_id"; $res = $db->query($sql); $prevname=''; $tdata = ''; $studata = $newarray; foreach ($res as $row) { if ($row['name'] != $prevname) { if ($prevname) { $tdata .= "<tr><td>$prevname</td>"; foreach ($studata as $cert=>$marks) { $cclass = "L$cert"; $certres = min($marks) >= 50 ? "Entitle for Cert $cert" : 'Complete only'; foreach ($marks as $m) { $tdata .= "<td class='$cclass'>$m</td>"; } $tdata .= "<td class='cert$cclass'>$certres</td>"; } $tdata .= "</tr>\n"; } $studata = $newarray; $prevname = $row['name']; } $studata[$row['certlevel']][$row['course']] = $row['marks']; } // last student $tdata .= "<tr><td>$prevname</td>"; foreach ($studata as $cert=>$marks) { $cclass = "L$cert"; $certres = min($marks) >= 50 ? "Entitle for Cert $cert" : 'Complete only'; foreach ($marks as $m) { $tdata .= "<td class='$cclass'>$m</td>"; } $tdata .= "<td class='cert$cclass'>$certres</td>"; } $tdata .= "</tr>\n"; ?> <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <style type="text/css"> body { font-family: sans-serif; font-size: 10pt; } table { border-collapse: collapse; } td, th { border: 1px solid #888; padding: 3px; min-width: 40px; } td.L1 { background-color: #ccf; text-align: right; } td.L2 { background-color: #fcc; text-align: right; } td.certL1 { background-color: #ccf; font-weight: 600; } td.certL2 { background-color: #fcc; font-weight: 600; } th.L1 { background-color: #aaf; } th.L2 { background-color: #faa; } </style> </head> <body> <table> <thead> <?=$thead?> </thead> <tbody> <?=$tdata?> </tbody> </table> </body> </html> NOTE The pdo connect function (in an included file) is
define("HOST",'localhost'); define("USERNAME",'*****************'); define("PASSWORD",'*****************'); function pdoConnect($dbname) { $db = new PDO("mysql:host=".HOST.";dbname=$dbname",USERNAME,PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $db; }
-
Barand's post in PHP multiple date condition was marked as the answer
try
<?php // get data date range $date = new DateTime(); // now $dateto = $date->format('Y-m-01'); $datefrom = $date->sub(new DateInterval('P1Y'))->format('Y-m-01'); // minus 1 year // initialize array $dp = new DatePeriod($date, new DateInterval('P1M'),12); $data = []; foreach ($dp as $d) { $data[$d->format('F')] = [ 'reg' => 0, 'app' => 0 ]; } // get data and accumulate counts in array $sql = "SELECT registerdate , approvedate FROM datetest WHERE registerdate >= ? AND registerdate < ? ORDER BY registerdate"; $stmt = $pdo->prepare($sql); $stmt->execute( [$datefrom, $dateto] ); while ($row = $stmt->fetch()) { if ($row['registerdate']) { $rm = (new DateTime($row['registerdate']))->format('F'); $data[$rm]['reg']++; } if ($row['approvedate']) { $am = (new DateTime($row['approvedate']))->format('F'); $data[$am]['app']++; } } // assemble the output $tabledata = ''; foreach ($data as $month=>$vals) { $tabledata .= "<tr><td>$month</td><td>{$vals['reg']}</td><td>{$vals['app']}</td></tr>\n"; } ?> <table> <thead> <tr><th>Month</th><th>Registered</th><th>Approved</th></tr> </thead> <tbody> <?=$tabledata?> </tbody> </table> Note that this processes the previous year's data. Set your date range as required.