Barand

I use SQL variables to save repetition of bound parameters. For example $sql = "SELECT FirstName, LastName, Email, Mobile, PromoCode FROM <database1>.<table> JOIN (@srch := ?) init_var WHERE (Email LIKE @srch) OR (Mobile LIKE @srch) OR (PromoCode LIKE @srch) UNION SELECT FirstName, LastName, Email, Mobile, PromoCode FROM <database2>.<table> WHERE (Email LIKE @srch) OR (Mobile LIKE @srch) OR (PromoCode LIKE @srch) UNION SELECT FirstName, LastName, Email, Mobile, PromoCode FROM <database3>.<table> WHERE (Email LIKE @srch) OR (Mobile LIKE @srch) OR (PromoCode LIKE @srch) "; $stmt = $db->prepare($sql); $search = '%xxx%'; $stmt->bind_param('s', $search); Note the first has a subquery to set the varaible value to the param value.

1 ) No one has mentioned a LEFT join. 2 ) If you read the replies you would see that taquitosensei spoonfed you the solution (reply #3)

I chose June for a test month as it was the first one I found with 5 Fridays.

That's what I said.

->d gives the number of days left after the months have been calculated ->days gives the total days difference EG $startdate = new DateTime('2017-01-01'); $enddate = new dateTime(); // today // output difference in months and days $months = $enddate->diff($startdate)->m; $days = $enddate->diff($startdate)->d; echo "$months months $days days<br>"; // 1 months 20 days // output difference in days only $days = $enddate->diff($startdate)->days; // 51 days echo "$days days";

You need to convert the database datetime field to a PHP DateTime object $dtobj = new DateTime($dbdatetime);

I think you need $diff->days and not $diff->d

Each hashtag should be in its own row in a separate table (google database normalization)

The algorithm seems to break down in June June 2017 Su Mo Tu We Th Fr Sa -------------------- 1 2 2 2 2 2 2 2 3 3 3 3 3 3 3 4 4 4 4 4 4 4 5 5 5 1 1 1 1 1

Sounds like you want a cartesian join (cross join). What have you tried so far?

Follow the link that benanamen gave you in reply #2 above

You would use a UNION in the query to get the fathers then the mothers. So the query becomes select sname , fname , foccup from nroll where fimp = 'Y' UNION select sname , mname , moccup from nroll where mimp = 'Y' order by foccup, sname

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>

No. header() returns no value.

So, given this input ... +----------+---------+-------+-----------------+---------+------------------+------+ | nroll_id | sname | fname | foccup | mname | moccup | fimp | +----------+---------+-------+-----------------+---------+------------------+------+ | 1 | Mary | Peter | butcher | Jessica | baker | Y | | 2 | Jane | Paul | baker | Yvonne | candlestickmaker | Y | | 3 | Peter | Jack | butcher | Kath | butcher | Y | | 4 | Matthew | Henry | candlstickmaker | Naomi | doctor | Y | | 5 | Zeke | Steve | baker | Sarah | banker | Y | +----------+---------+-------+-----------------+---------+------------------+------+ ... what would the output look like?

You don't show the full form html, so I am guessing that each height/weight pair is in a separate form and not, as they should be, all in the same form with a single submit.

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 | +----+----------+----------+------+--------+

Following the rule described in the last paragraph, how did user4 and user5 get inserted into two different row-positions?

No, you are still pulling the a_id and h_id magically from nowhere. Try INSERT INTO match_summ(MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID,Team_catId) SELECT 14,6,7,8,9, IFNULL(team_catid, 0) FROM ( SELECT a_id as teamid FROM matches UNION SELECT h_id as teamid FROM matches ) teams LEFT JOIN teamcat tc USING (teamid); I'd be interested in seeing the entire data model for this application. It has a very weird feel to it.

Include those columns in the query selection and add them to the output of the results.

Which is it?

According to your data, none of the teams in the matches table have a corresponding team_catid in the catID table. Is this a possible situation?

You peeped! Your connection code is using the obsolete mysql_ library. Use mysqli_ or PDO connection

Make the primary key (pet_type, pet_id) where the pet_id is auto_increment. (This increments it within the pet type) EG mysql> CREATE TABLE `pet` ( -> `user_id` int(11) DEFAULT NULL, -> `pet_type` varchar(45) NOT NULL, -> `pet_id` int(11) NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`pet_type`,`pet_id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.34 sec) mysql> INSERT INTO pet (user_id, pet_type) VALUES -> (1, 'cat'), -> (2, 'cat'), -> (2, 'dog'), -> (3, 'cat'), -> (4, 'dog'); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM pet; +---------+----------+--------+ | user_id | pet_type | pet_id | +---------+----------+--------+ | 1 | cat | 1 | | 2 | cat | 2 | | 2 | dog | 1 | | 3 | cat | 3 | | 4 | dog | 2 | +---------+----------+--------+

Two methods. Loop through the query results 1 ) When the occupation changes, start a new table (hence the order by occupation) or 2 ) store the results in an array indexed by occupation This is the second method (you may peep if you get stuck) And this is the first method (again, no peeping)