Search the Community

In MySQL, I want to create a trigger with AFTER UPDATE triggering event for my "user" table. In next, I have a table named "user_log" which is use to store the modifications that occurred on the parent table "user" after any update commands. So, data in "user_log" table need be as follows: select * from user_log; +--------+---------+----------------------------+---------------+----------------+---------------------+------+ | log_id | user_id | action | old_data | new_data | changed_date | read | +--------+---------+----------------------------+---------------+----------------+---------------------+------+ | 1 | 10 | Changed yyy's name | yyy | xxx | 2022-06-20 14:06:56 | no | | 2 | 10 | Changed xxx's address | No.111, | No.112, | 2022-06-20 19:07:38 | no | | 3 | 10 | Changed xxx's city | Old City Name | New City Name | 2022-06-20 19:07:38 | no | | 4 | 10 | Changed xxx's phone number | 011-5000000 | 011-4000000 | 2022-06-20 19:07:38 | no | +--------+---------+----------------------------+---------------+----------------+---------------------+------+ As you can see from the data in the table above, it will update several columns at once. So I created my triger as follows, and its working for me. DELIMITER $$ DROP TRIGGER IF EXISTS `user_log` ; $$ CREATE TRIGGER `user_log` AFTER UPDATE ON `user` FOR EACH ROW BEGIN IF OLD.name <> NEW.name THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.name <> OLD.name) THEN CONCAT('Changed ', OLD.name, "'s ", 'name') ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN OLD.name ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN NEW.name ELSE '' END ); END IF; IF OLD.address <> NEW.address THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.address <> OLD.address) THEN CONCAT('Changed ', OLD.name, "'s ", 'address') ELSE '' END , CASE WHEN (NEW.address <> OLD.address) THEN OLD.address ELSE '' END , CASE WHEN (NEW.address <> OLD.address) THEN NEW.address ELSE '' END ); END IF; IF OLD.city <> NEW.city THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.city <> OLD.city) THEN CONCAT('Changed ', OLD.name, "'s ", 'city') ELSE '' END , CASE WHEN (NEW.city <> OLD.city) THEN OLD.city ELSE '' END , CASE WHEN (NEW.city <> OLD.city) THEN NEW.city ELSE '' END ); END IF; IF OLD.phone <> NEW.phone THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.phone <> OLD.phone) THEN CONCAT('Changed ', OLD.name, "'s ", 'phone number') ELSE '' END , CASE WHEN (NEW.phone <> OLD.phone) THEN OLD.phone ELSE '' END , CASE WHEN (NEW.phone <> OLD.phone) THEN NEW.phone ELSE '' END ); END IF; END$$ DELIMITER ; My problem is, I have a lot more columns in the user table. Like I said, all columns or several of them are updated at once. In that case I have to add a large amount of INSERT query to my trigger. So here I would like to know if there is another suitable way to do this. I also tried it in this way. But its working only for one column. DROP TRIGGER IF EXISTS `user_log`; CREATE TRIGGER IF NOT EXISTS `user_log` AFTER UPDATE ON user FOR EACH ROW INSERT INTO user_log (user_id,action,old_data,new_data) VALUES ( NEW.user_id , CASE WHEN (NEW.name <> OLD.name) THEN CONCAT('Changed ', OLD.name, "'s ", 'name') WHEN (NEW.address <> OLD.address) THEN CONCAT('Changed ', OLD.name, "'s ", 'address') WHEN (NEW.city <> OLD.city) THEN CONCAT('Changed ', OLD.name, "'s ", 'city') WHEN (NEW.phone <> OLD.phone) THEN CONCAT('Changed ', OLD.name, "'s ", 'phone number') ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN OLD.name WHEN (NEW.address <> OLD.address) THEN OLD.address WHEN (NEW.city <> OLD.city) THEN OLD.city WHEN (NEW.phone <> OLD.phone) THEN OLD.phone ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN NEW.name WHEN (NEW.address <> OLD.address) THEN NEW.address WHEN (NEW.city <> OLD.city) THEN NEW.city WHEN (NEW.phone <> OLD.phone) THEN NEW.phone ELSE '' END ); Thank you.

Hi guys! I've tried to insert data inside an input's value but the input goes like it is hidden, When I inspect the page it shows that there is no input inside my form. I've tried to move the code to the top of page but nothing is changed always a hidden input while it is not hidden at all as you can see below: <div class="mb-3"> <?php //Checking if submit button is clicked if (isset($_POST['submit'])) { //database cn $db = new PDO("mysql:host=localhost;dbname=centrify","root",""); $username = $_POST['user']; $stmt = $db->prepare("SELECT * FROM agencies_data WHERE agency_user = ".$username.""); $stmt->execute(); ?> <input class="form-control" type="text" name="oid" value="<?php while($item = $stmt->fetch()) { echo $item['agency_user']; } ?>"> <?php } ?> </div> I've tested a lot of placements but it doesnt work for me.

I have two mysql tables. The two tables with the sample data are as follows. select * from stock; +----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | +----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+ | 48 | v | 44.00 | 1 | 1 | 1 | 38 | 2022-05-16 14:27:19 | | 49 | v | 8.00 | 263 | 1 | 1 | 38 | 2022-05-16 14:27:19 | | 50 | a | 6.00 | 1 | 1 | 1 | 39 | 2022-05-16 14:30:04 | | 51 | a | 4.00 | 263 | 1 | 1 | 39 | 2022-05-16 14:30:04 | | 56 | a | 28.00 | 1 | 1 | 1 | 41 | 2022-05-16 14:51:59 | | 57 | a | 57.00 | 263 | 1 | 1 | 41 | 2022-05-16 14:51:59 | | 58 | a | 6.00 | 264 | 1 | 1 | 41 | 2022-05-16 14:51:59 | | 59 | a | 19.00 | 301 | 1 | 1 | 41 | 2022-05-16 14:51:59 | | 64 | a | 15.00 | 263 | 1 | 5 | 0 | 2022-05-18 17:23:37 | | 65 | a | 546.00 | 264 | 1 | 5 | 0 | 2022-05-18 17:23:37 | | 66 | a | 15.00 | 263 | 1 | 5 | 0 | 2022-05-18 17:24:21 | | 67 | a | 546.00 | 264 | 1 | 5 | 0 | 2022-05-18 17:24:21 | | 72 | v | 20.00 | 720 | 1 | 1 | 44 | 2022-05-24 09:24:43 | | 73 | v | 2.00 | 729 | 1 | 1 | 44 | 2022-05-24 09:24:43 | +----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+ select * from sales; +----------+---------+----------+-----------+ | sales_id | item_id | quantity | basket_id | +----------+---------+----------+-----------+ | 7 | 1 | 20.00 | 4 | | 8 | 263 | 3.00 | 4 | | 9 | 1 | 2.00 | 5 | | 10 | 263 | 4.00 | 5 | | 11 | 264 | 6.00 | 5 | | 12 | 301 | 1.00 | 5 | +----------+---------+----------+-----------+ By this I want to build up a update query. This should deduct the quantity of the items in the sales table from the stock table. If such a query can be created in mysql it should be updated in ascending order on the stock_id in the stock table. If such an update query can be built, based on the data in the two tables above, I expect the result to be as follows. select stock_id, qty_type, qty, item_id from stock where qty_type = 'a'; +----------+----------+--------+---------+ | stock_id | qty_type | qty | item_id | +----------+----------+--------+---------+ | 50 | a | 0.00 | 1 | -- clear by sales | 51 | a | 0.00 | 263 | -- clear by sales | 56 | a | 12.00 | 1 | -- deduct qty by sales | 57 | a | 54.00 | 263 | -- deduct qty by sales | 58 | a | 0.00 | 264 | -- clear by sales | 59 | a | 18.00 | 301 | -- deduct qty by sales | 64 | a | 15.00 | 263 | | 65 | a | 546.00 | 264 | | 66 | a | 15.00 | 263 | | 67 | a | 546.00 | 264 | +----------+----------+--------+---------+ Any help would be highly appreciated.

I am trying to develop an PHP MySQL database application where edit details is not working.I am new to PHP and doing this with the help of various web resources such as youtube videos, tutorials, similar programs etc .I am able to fetch the data from the database, but when it comes to edit, the data remains the same even after changing.Can anyone suggest the solution of this problem. manage-profile.php <?php session_start(); require('connection.php'); //If your session isn't valid, it returns you to the login screen for protection if(empty($_SESSION['sl_no'])){ header("location:access-denied.php"); } //retrive student details from the student table $result=mysqli_query($con, "SELECT * FROM student WHERE sl_no = '$_SESSION[sl_no]'"); if (mysqli_num_rows($result)<1){ $result = null; } $row = mysqli_fetch_array($result); if($row) { // get data from db $stdId = $row['sl_no']; $stdRoll = $row['roll_no']; $stdName = $row['name']; $stdClass = $row['class']; $stdSex= $row['sex']; } ?> <?php // updating sql query if (isset($_POST['update'])){ $myId = addslashes( $_GET[$id]); $myRoll = addslashes( $_POST['roll_no'] ); $myName = addslashes( $_POST['name'] ); $myClass = addslashes( $_POST['class'] ); $myGender = $_POST['sex']; $sql = mysqli_query($con,"UPDATE student SET roll_no='$myRoll', name='$myName', class='$myClass', sex='$myGender' WHERE sl_no = '$myId'" ); // redirect back to profile header("Location: manage-profile.php"); } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Student Profile Management</title> <link href="css/student_styles.css" rel="stylesheet" type="text/css" /> <script language="JavaScript" src="js/user.js"> </script> </head> <body bgcolor="#e6e6e6"> <center><b><font color = "black" size="6">Online Voting System</font></b></center><br><br> <div id="page"> <div id="header"> <h2>Manage Profile</h2> <a href="student.php">Home</a> | <a href="vote.php">Current Polls</a> | <a href="manage-profile.php">Manage My Profile</a> | <a href="changepassword.php">Change Password</a>| <a href="logout.php">Logout</a> </div> <div id="container"> <table border="0" width="620" align="center"> <CAPTION><h3>Update Profile</h3></CAPTION> <form action="manage-profile.php?$id=<?php echo $_SESSION['sl_no']; ?>" method="post" onsubmit="return updateProfile(this)"> <table align="center"> <tr><td>Roll Number:</td><td><input type="text" style="background-color:#e8daef; font-weight:regular;" name="roll_no" maxlength="50" value="<?php echo $row["roll_no"]; ?>"></td></tr> <tr><td>Name:</td><td><input type="text" style="background-color:#e8daef; font-weight:regular;" name="Name" maxlength="30" value="<?php echo $row["name"]; ?>"></td></tr> <tr><td>Class:</td><td><select name='sclass' style='background-color:#e8daef; font-weight:regular;' maxlength='10' id='class' required='true'> <option value='HS-1st Year' <?php if($row["class"]=='HS-1st Year') { echo "selected"; } ?> >HS-1st Year</option> <option value='HS-2nd Year' <?php if($row["class"]=='HS-2nd Year') { echo "selected"; } ?> >HS-2nd Year</option> <option value='BA-1st Sem' <?php if($row["class"]=='BA-1st Sem') { echo "selected"; } ?> >BA-1st Sem</option> <option value='BA-3rd Sem' <?php if($row["class"]=='BA-3rd Sem') { echo "selected"; } ?> >BA-3rd Sem</option> <option value='BA-5th Sem' <?php if($row["class"]=='BA-5th Sem') { echo "selected"; } ?> >BA-5th Sem</option> <option value='BCom-1st Sem' <?php if($row["class"]=='BCom-1st Sem') { echo "selected"; } ?> >BCom-1st Sem</option> <option value='BCom-3rd Sem' <?php if($row["class"]=='BCom-3rd Sem') { echo "selected"; } ?> >BCom-3rd Sem</option> <option value='BCom-5th Sem' <?php if($row["class"]=='BCom-5th Sem') { echo "selected"; } ?> >BCom-5th Sem</option> </select> </td></tr> <tr><td>Sex:</td><td> <input type='radio' style='background-color:#e8daef; font-weight:regular;' name='gender' id='male' value='Male' <?php if($row["sex"]=='Male') { echo "checked"; } ?> >Male<br> <input type='radio' style='background-color:#e8daef; font-weight:regular;' name='gender' id='female' value='Female' <?php if($row["sex"]=='Female') { echo "checked"; } ?> >Female<br></td></tr> <tr><td>&nbsp;</td></tr><tr><td><input type="submit" name="update" value="Update Profile"></td></tr> </table> </form> </div> <div id="footer"> <div class="bottom_addr">Student Union Election,Anonymous College</div> </div> </body> </html>

Ok - I have a working query to which I wanted to add one more item from a new table. Thought it was easy but I must be having a bad day. I will post the original working query (now commented out) and the new query with the added item 'driver_num': /* $q = "select a.race_winner, w.wins, substr(a.race_winner, instr(a.race_winner,' ')+1) as last_name, a.race_name, date_format(a.race_date, '%m/%d') as race_date from trk_races a left outer join (select race_winner, count(race_date) as wins from trk_races where race_winner > '' and Season='$selyr' group by race_winner) w on w.race_winner = a.race_winner where a.race_winner > '' and a.Season='$selyr' order by $orderby"; */ $q = "select a.race_winner, w.wins, substr(a.race_winner, instr(a.race_winner,' ')+1) as last_name, a.race_name, date_format(a.race_date, '%m/%d') as race_date, d.driver_num from trk_races a, drivers d left outer join (select race_winner, count(race_date) as wins from trk_races where race_winner > '' and Season='$selyr' group by race_winner) w on w.race_winner = a.race_winner where a.race_winner > '' and a.Season = '$selyr' and d.driver_season = a.Season and a.race_winner = d.driver_name order by $orderby"; The only addition is the "drivers d" in the from clause of the first select along with the additions to the ending where clause that connects the "drivers" table to the "trk_races" table in that select. Here is the error I am getting from this change: Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'a.race_winner' in 'on clause' in /home/albany/public_html/homejg/nascar/nas_show_drivers.php:129 Stack trace: #0 /home/albany/public_html/homejg/nascar/nas_show_drivers.php(129): PDO->query('select a.race_w...') ..... The error column is already being used elsewhere in the query so I can't figure out why I am getting this error.

For the life of me I can not figure out the syntax error. I was thinking it was related to DateTime field. The data comes over as a string from the api, but converting to time doesn't work. The field in the database is DateTime. Any suggestions would be appreciated. The data from the api for reference. array(1) { [0]=> array(3) { ["macAddress"]=> string(17) "FA:F5:C2:98:6F:29" ["lastData"]=> array(27) { ["dateutc"]=> int(1642622880000) ["tempinf"]=> float(77.9) ["humidityin"]=> int(38) ["baromrelin"]=> float(29.956) ["baromabsin"]=> float(29.132) ["tempf"]=> float(77.2) ["battout"]=> int(1) ["humidity"]=> int(47) ["winddir"]=> int(247) ["windspeedmph"]=> float(5.6) ["windgustmph"]=> float(8.1) ["maxdailygust"]=> float(15.9) ["hourlyrainin"]=> int(0) ["eventrainin"]=> int(0) ["dailyrainin"]=> int(0) ["weeklyrainin"]=> int(0) ["monthlyrainin"]=> float(0.52) ["totalrainin"]=> float(48.862) ["solarradiation"]=> float(196.47) ["uv"]=> int(1) ["feelsLike"]=> float(76.83) ["dewPoint"]=> float(55.39) ["feelsLikein"]=> float(77.2) ["dewPointin"]=> float(50.2) ["lastRain"]=> string(24) "2022-01-12T02:50:00.000Z" ["tz"]=> string(15) "America/Chicago" ["date"]=> string(24) "2022-01-19T20:08:00.000Z" } ["info"]=> array(2) { ["name"]=> string(18) "My Weather Station" ["coords"]=> array(5) { ["coords"]=> array(2) { ["lon"]=> float(-197.65635809999999) ["lat"]=> float(38.6587316) } ["address"]=> string(44) "100 Main Street, Anytown, FL 08226, USA" ["location"]=> string(10) "Anytown" ["elevation"]=> float(214.7066497802734) ["geo"]=> array(2) { ["type"]=> string(5) "Point" ["coordinates"]=> array(2) { [0]=> float(-97.65635809999999) [1]=> float(30.6587316) } } } } } } I take the data from the array and assign it to variables. Notice the unsuccessful attempts to convert string to DateTime. <?php $_DateTime = $data[0]['lastData']['date']; //$_OldDateTime = $data[0]['lastData']['date']; //$_dateTime = strtotime($_OldDateTime); $_tempf = $data[0]['lastData']['tempf']; $_feelsLike = $data[0]['lastData']['feelsLike']; $_stationbarometer = $data[0]['lastData']['baromrelin']; $_sealevelbarometer = $data[0]['lastData']['baromabsin']; $_dewpoint = $data[0]['lastData']['dewPoint']; $_humidity = $data[0]['lastData']['humidity']; $_winddir = $data[0]['lastData']['winddir']; $_windspeed = $data[0]['lastData']['windspeedmph']; $_gust = $data[0]['lastData']['windgustmph']; $_maxdailygust = $data[0]['lastData']['maxdailygust']; $_hourlyrainrate = $data[0]['lastData']['hourlyrainin']; $_dailyrain = $data[0]['lastData']['dailyrainin']; $_weeklyrain = $data[0]['lastData']['weeklyrainin']; $_monthlyrain = $data[0]['lastData']['monthlyrainin']; $_totalrain = $data[0]['lastData']['totalrainin']; $_lastRain = $data[0]['lastData']['lastRain']; //$_OldlastRain = $data[0]['lastData']['lastRain']; //$_lastRain = strtotime($_OldlastRain); $_solar = $data[0]['lastData']['solarradiation']; $_battout = $data[0]['lastData']['battout']; // this is where we insert into the database $sql = "INSERT INTO weather_data (datetime, tempf, feelslike, stationbarometer, sealevelbarometer, dewpoint, humidity, winddir, windspeed, gust, maxdailygust, hourlyrainrate, dailyrain, weeklyrain, monthlyrain, totalrain, lastrain, solar, battout) VALUES ( $_DateTime, $_tempf, $_feelsLike, $_stationbarometer, $_sealevelbarometer, $_dewpoint, $_humidity, $_winddir, $_windspeed, $_gust, $_maxdailygust, $_hourlyrainrate, $_dailyrain, $_weeklyrain, $_monthlyrain, $_totalrain, $_lastRain, $_solar, $_battout )"; //mysqli_query($conn, $sql); if (mysqli_query($conn, $sql)) { echo "Success!"; //json_encode(array("statusCode"=>200)); } else { echo "Error: " . $sql . "<br>" . mysqli_error($conn); } mysqli_close($conn); ?> Error message: Error: INSERT INTO weather_data (datetime, tempf, feelslike, stationbarometer, sealevelbarometer, dewpoint, humidity, winddir, windspeed, gust, maxdailygust, hourlyrainrate, dailyrain, weeklyrain, monthlyrain, totalrain, lastrain, solar, battout) VALUES ( 2022-01-24T15:40:00.000Z, 47.7, 47.7, 30.048, 29.224, 45.49, 92, 314, 1.3, 2.2, 8.1, 0, 0.161, 0.161, 0.681, 49.024, 2022-01-24T15:34:00.000Z, 29.48, 1 ) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':40:00.000Z, 47.7, 47.7, 30.048, 29.224, 45.49, 92, 314, ' at line 6

In my DB schema, there are 4 tables and its relationship as shown in the below attached image. My question is how many schools are there according to the data in the school table and how many of those schools belong to provinces, districts and zones, is it possible in one mysql query? Currently I am using 4 separate queries for this and the relevant code is as follows. $sql = "SELECT count(school_id) as schtot FROM sn_school"; $stmt = $pdo->query($sql); $schtot = $stmt->fetchColumn(); $schtot = str_pad($schtot , 3, 0, STR_PAD_LEFT); $sql = "SELECT count(zone_id) as zonetot FROM sn_school GROUP BY zone_id"; $stmt = $pdo->query($sql); $zonetot = $stmt->rowCount(); $zonetot = str_pad($zonetot , 3, 0, STR_PAD_LEFT); $sql = "SELECT COUNT(d.district_id) as districtTot FROM sn_school s JOIN zone z USING(zone_id) JOIN district d ON d.district_id = z.district_id GROUP BY d.district_id"; $stmt = $pdo->query($sql); $districtTot = $stmt->rowCount(); $districtTot = str_pad($districtTot , 3, 0, STR_PAD_LEFT); $sql = "SELECT COUNT(p.province_id) as ptot FROM sn_school s JOIN zone z USING(zone_id) JOIN district d ON d.district_id = z.district_id JOIN district p ON p.province_id = d.province_id GROUP BY p.province_id"; $stmt = $pdo->query($sql); $ptot = $stmt->rowCount(); $ptot = str_pad($ptot , 3, 0, STR_PAD_LEFT);

I have a query I'm using in my CodeIgniter model to fetch the count of listings of products between particular days. What this query is doing is it's taking status_from from logs where the date is during and after the selected date range and taking added_date from listings where the date falls before the from date range picked by the user and calculates it. Once it has retrieved those records, it checks the table for what variable that status holds and does a sum(case when else 0) to get the total count. function get_report(){ $sql = with Y as ( with recursive D (n, day) as ( select 1 as n, '2021-09-25' my_date union select n+1, day + interval 1 day from D where day + interval 1 day < '2021-10-15' ) select * from D ), X as ( select Y.day, l.*, (select status_from from logs where logs.refno = l.refno and logs.logtime >= Y.day order by logs.logtime limit 1) logstat from listings l, Y where l.added_date <= Y.day ), Z as ( select X.day, ifnull(X.logstat,X.status) stat_day, count(*) cnt from X group by X.day, stat_day ) select Z.day, sum(case when Z.stat_day = 'D' then Z.cnt else 0 end ) Draft, sum(case when Z.stat_day = 'A' then Z.cnt else 0 end ) Action, sum(case when Z.stat_day = 'Y' then Z.cnt else 0 end ) Publish, sum(case when Z.stat_day = 'S' then Z.cnt else 0 end ) Sold, sum(case when Z.stat_day = 'L' then Z.cnt else 0 end ) Let from Z group by Z.day order by Z.day; $query = $this->db->query($sql); return $query; } Dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6789f0517b194b09d235860dc794ea14 Now here as you can see I'm processing my calculations in the sql statement itself, but I want to do these calculations in my php side, basically iterating everytime it encounters Z.stat_day = 'D' then adding 1 to Draft column and same for the other statuses. Current View Class: <?php foreach($data_total as $row ){ $draft = $row->draft ? $row->draft : 0; $publish = $row->publish ? $row->publish : 0; $action = $row->action ? $row->action : 0; $sold = $row->sold ? $row->sold : 0; $let = $row->let ? $row->let : 0; ?> <tr> <td><?= $row->day?></td> <td><?= $draft ?></td> <td><?= $publish ?></td> <td><?= $action ?></td> <td><?= $sold ?></td> <td><?= $let ?></td> </tr> <?php } ?> Basically I want to refractor my PHP code to do the same thing the Mysql statement is doing, but keeping the query simple and all processing in the PHP side for performance reasons.

Currently I have a table that displays a total count of my data values for each source. I'm getting this value after comparing 2 tables 1 is crm_leads with all my product information and 1 is crm_sources with what sources are the products related to. Now this is the output: Now as you can see the total count is shown under each header next to its source. Now these count values are inside a tags which once clicked go to viewall page. Here basically I want to show the data of the item that I had clicked. So for example, if I clicked the 163 under Hot status, it takes me to the view all page and shows me id, source, enquiry_date for all those under status Hot in a table. So basically it should detect the data for which source and which status is clicked and then accordingly make a statement like this? select * from crm_leads where lead_source = '.$source.' and lead_status = '.$status.'; Model Class: function get_statusreport($fdate='',$tdate='') { $this->db->select("l.lead_status,crm_sources.title,count(*) as leadnum,l.enquiry_date,l.sub_status"); $this->db->from($this->table_name." as l"); if($fdate !='') $this->db->where("date(l.added_date) >=",date('Y-m-d',strtotime($fdate))); if($tdate !='') $this->db->where("date(l.added_date) <=",date('Y-m-d',strtotime($tdate))); $this->db->where("lead_status <>",10); $this->db->join("crm_sources ","crm_sources.id= l.lead_source","left"); $this->db->group_by("l.lead_status,crm_sources.title"); $this->db->order_by("leadnum DESC, crm_sources.title ASC,l.lead_status ASC"); $query = $this->db->get(); $results = $query->result_array(); return $results; } Controller Class(leadstatus holds the view for my current table): public function leadstatus($slug='') { $content=''; $content['groupedleads'] = $this->leads_model->get_statusreport($fdate,$tdate); $this->load->view('crm/main',$main); $this->load->view('crm/reports/leadstatus',$content); } public function viewall($slug='') { $content=''; $this->load->view('crm/main',$main); $this->load->view('crm/reports/viewall',$content); } View class: <?php $ls_arr = array(1=>'Open',8=>'Hot',2=>'Closed',3=>'Transacted',4=>'Dead'); foreach($groupedleads as $grplead){ $statuses[] = $status = $ls_arr[$grplead["lead_status"]]; if($grplead["title"] == NULL || $grplead["title"] == '') $grplead["title"] = "Unknown"; if(isset($grplead["title"])) $titles[] = $title = $grplead["title"]; $leaddata[$status][$title] = $grplead["leadnum"]; } if(count($titles) > 0) $titles = array_unique($titles); if(count($statuses) > 0) $statuses = array_unique($statuses); ?> <table> <tr"> <th id="status">Source</th> <?php if(count($statuses) > 0) foreach($statuses as $status){ ?><th id=<?php echo $status; ?>><?php echo $status; ?></th> <?php } ?> <th>Total</th> </tr> <?php if(is_array($titles)) foreach($titles as $title){ ?> <tr> <?php $total = 0; echo "<td>".$title."</td>"; foreach ($statuses as $status) { $num = $leaddata[$status][$title]; echo "<td><a target='_blank' href='".site_url('reports/viewall')."'>".$num."</a></td>"; $total += $num; $sum[$status] += $num; } echo "<td>".$total."</td>"; $grandtotal += $total; ?> </tr> <?php } ?> </table>

Here is the dbfiddle for better understanding, refer this when reading question: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0919cacb5d117450168cdc917433a45e I have 2 tables called listings and logs table. The listings table holds a products reference number and it's current status. So suppose if it's status was Publish currently and it's sold later, the status updates to Sold. Here the refno. in this table is unique since the status can change for 1 product. Now I have another table called Logs table, this table records all the status changes that have happened for a particular product(referenced by refno) in a particular timeframe. The initial entry in the listings table is not recorded here, but once it's status is changed, that entry is shown here. Suppose I have the following Listings table('D' => 'Draft', 'A' => 'Action', 'Y' => 'Publish', 'S' => 'Sold', 'N' => 'Let'): INSERT INTO listings VALUES (3, 'Y','2021-05-02','2021-10-02','LP01'), (4, 'A','2021-05-01','2021-05-01','LP02'), (5, 'S','2020-10-01','2020-10-01','LP03'), (6, 'N','2021-05-01','2021-10-06','LP06'), (10, 'D','2021-10-06','2021-10-06','LP05'), (11, 'D','2021-01-01','2021-01-01','LP04'); Here as of now the total count under every status would give: |status_1|c| |:---:|:--:| |Publish|1| |Action|1| |Sold|1| |Left|1| |Draft|2| But if I wanted only the count for entries made in 2020-10-01 it'll show 0 under all statuses except sold, where it'll show 1. Now in this timeframe between 2020-10-01 and today, there have been values entered in listings table as shown above and also for some, the statuses have changed. Status table: INSERT INTO logs VALUES (1, 'Let','Action','2021-06-01','LP01'), (2, 'Action','Draft','2021-10-01','LP01'), (3, 'Draft','Publish','2021-10-02','LP01'), (4, 'Action','Let','2021-10-06','LP06'); What is being shown right now in my listings table is the values after the status change has been made. So now to get the total count on a particular day, I'm having my statement reference the dates from the logs table and respectively subtract the status_to, and add the status_from. Query for this is in the dbfiddle provided above. Here I made it to return data that happened on or before 2021-10-01 and it does not give the right output. Another problem with this query is I cannot return the data for the entries that had initially taken place. For example like I mentioned above the value for the data on 2020-10-01 should show 1 under sold, while 0 under everything else(desired output), but it does not do this since there are no logs made in logs table for when a new entry in initially added. So basically what I want here is to get the initial entries as well with the same entry. If you want an easier explanation for what I'm trying to achieve, please refer to this:

Currently I have 2 tables, the first table shows a count of statuses, refno. and agent_id(person in charge of the refno.) and the second table has an id and agent_name. So to refer a particular agent next to the refno. in table 1, you can reference it via the id of the agent table. Dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d0e8222a1e49774bcfbcfa30cec75732 Now I have found out that some of my listings have the agent_id as 0 and null, which doesn't have a reference in my agents table. So here I'm using COALESCE to add an extra row called Unassigned and inserting all variables with agent_id 0 or null inside this column. I've tried this same in my codeigniter model: function get_totalagentstatus(){ $this->db->select("SUM(CASE WHEN t.status = 'D' THEN 1 END) AS draft, SUM(CASE WHEN t.status = 'N' THEN 1 END) AS unpublish, SUM(CASE WHEN t.status = 'Y' THEN 1 END) AS publish, SUM(CASE WHEN t.status = 'U' THEN 1 END) AS action, SUM(CASE WHEN t.status = 'L' THEN 1 END) AS unlisted, SUM(CASE WHEN t.status = 'S' THEN 1 END) AS sold, SUM(CASE WHEN t.status = 'T' THEN 1 END) AS let, COALESCE(c.display_name,'Unassigned'), SUM(t.status = 'D') +SUM(t.status = 'N') + SUM(t.status = 'Y') + SUM(t.status = 'U') + SUM(t.status = 'L' ) + SUM(t.status = 'S' )+ SUM(t.status = 'T' ) AS total, t.agent_id, c.display_name"); $this->db->from('crm_listings t'); $this->db->join('crm_clients_users c','t.agent_id = c.id'); $this->db->where('archive="N"'); $this->db->group_by('COALESCE(c.display_name,"Unassigned")'); $results = $this->db->get(); return $results; } Controller Class: $content['total_agent_status'] = $this->leads_model->get_totalagentstatus()->result(); View Class: <?php foreach($total_agent_status as $row ){ $draft = $row->draft ? $row->draft : 0; $unpublish = $row->unpublish ? $row->unpublish : 0; $publish = $row->publish ? $row->publish : 0; $action = $row->action ? $row->action : 0; $unlisted = $row->unlisted ? $row->unlisted : 0; $sold = $row->sold ? $row->sold : 0; $let = $row->let ? $row->let : 0; $total = $row->total ? $row->total : 0; ?> <tr> <td><?= $row->display_name ?></td> <td><?= $draft ?></td> <td><?= $unpublish ?></td> <td><?= $publish ?></td> <td><?= $action ?></td> <td><?= $unlisted ?></td> <td><?= $sold ?></td> <td><?= $let ?></td> <td><?= $total ?></td> </tr> Now this returns everything except the Unassigned row which I want. I've also input this in my phpmyadmin to see the result and it does not return it there either, instead it shows the output with these headers and Unassigned is not there in any of the entries here:

Currently I'm using CodeIgniter and Mysql to fetch my data. Here I'm using the following model to get a count of each status from my database: function get_total(){ $this->db->select("SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, SUM(CASE WHEN status_to = 'Unpublish' THEN 1 END) AS unpublish, SUM(CASE WHEN status_to = 'Publish' THEN 1 END) AS publish, SUM(CASE WHEN status_to = 'Action' THEN 1 END) AS action, SUM(CASE WHEN status_to = 'Unlisted' THEN 1 END) AS unlisted, SUM(CASE WHEN status_to = 'Sold' THEN 1 END) AS sold, SUM(CASE WHEN status_to = 'Let' THEN 1 END) AS let"); $this->db->from('crm_logs'); $results = $this->db->get(); return $results; } Then to get this model I've used the following controller class: public function totallistings($slug='') { $fdate = $this->input->post("fdate"); $tdate = $this->input->post("tdate"); if ($fdate) { $content['fdate'] = $fdate; } else { $content['fdate'] = ''; } if ($tdate) { $content['tdate'] = $tdate; } else { $content['tdate'] = ''; } $content['data_total'] = $this->leads_model->get_total()->result(); $main['content']=$this->load->view('crm/reports/totallistings',$content,true); $this->load->view('crm/main',$main); } And this is my View class: <?php if(isset($data_sum) && count($data_sum) > 0) { foreach($data_sum as $row ){ $draft = $row->draft ? $row->draft : 0; $unpublish = $row->unpublish ? $row->unpublish : 0; $publish = $row->publish ? $row->publish : 0; $action = $row->action ? $row->action : 0; $unlisted = $row->unlisted ? $row->unlisted : 0; $sold = $row->sold ? $row->sold : 0; $let = $row->let ? $row->let : 0; ?> <tr> <td><?= $draft ?></td> <td><?= $unpublish ?></td> <td><?= $publish ?></td> <td><?= $action ?></td> <td><?= $unlisted ?></td> <td><?= $sold ?></td> <td><?= $let ?></td> </tr> <?php } } else { ?> <?php } ?> I now found out that there are more status words other than draft, publish, etc in my database. So I wanted a way to make this `<th>` and `<td>` dynamic but still having the same functionality it is currently having.

Currently I'm using CodeIgniter MVC framework and Mysql to retrieve a count of data within a particular timeframe. My table data looks like this: Where there are different agents who are changing statuses in a particular timeframe. So what I want is to layout all the agents in the table and display what status change they had made in a particular timeframe. To do this I used the following model to get all the agents in the DB: function get_agent(){ $this->db->distinct(); $this->db->select("agent_id"); $this->db->from('crm_logs'); return $this->db->get(); } And this model to get a count of the status changes: function get_agentstatus($fdate,$tdate,$agent_id){ $this->db->select("SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, SUM(CASE WHEN status_to = 'Unpublish' THEN 1 END) AS unpublish, SUM(CASE WHEN status_to = 'Publish' THEN 1 END) AS publish, SUM(CASE WHEN status_to = 'Action' THEN 1 END) AS action, $this->db->from('crm_logs'); $this->db->where('cast(logtime as date) BETWEEN "' . $fdate . '" AND "' . $tdate . '" AND agent_id="'.$agent_id.'"'); $results = $this->db->get(); return $results; } In my controller class I have used the following code to get the models: public function agentlistings($slug='') { $fdate = $this->input->post("fdate"); $content['tdate'] = $tdate = $this->input->post("tdate"); if(isset($fdate)){ $content['fdate'] =$fdate; }else{ $content['fdate'] = ''; } if(isset($tdate)){ $content['tdate'] =$tdate; }else{ $content['tdate'] =''; } $content['agent'] = $this->leads_model->get_agent()->result_array(); $content['agent_status'] = $this->leads_model->get_agentstatus($fdate,$tdate,$content['agent'])->result_array(); $main['content']=$this->load->view('crm/reports/agentlistings',$content,true); $this->load->view('crm/main',$main); } And the following in my View class: <table id="statustbl" class="table-fill"> <thead> <tr style="height:<?php echo $height; ?>;"> <th>Agent</th> <th>Draft</th> <th>Unpublish</th> <th>Publish</th> <th>Action</th> </tr> </thead> <tbody class="table-hover"> <?php foreach ($agent as $row) { echo '<tr><td>' .$row['agent_id']. '</td></tr>';//column names } ?> </tbody> </table> With this code I'm able to fill up all my distinct agents into the agents column, But I do not know how to show the agent_status with thier respective agent_id. I have tried SELECT SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, SUM(CASE WHEN status_to = 'Unpublish' THEN 1 END) AS unpublish, SUM(CASE WHEN status_to = 'Publish' THEN 1 END) AS publish, SUM(CASE WHEN status_to = 'Action' THEN 1 END) AS action, agent_id FROM 'crm_logs' WHERE cast(logtime as date) BETWEEN "2021-09-25" AND "2021-10-01" GROUP BY agent_id to get the data in the same query, but this did not return the correct output.

Howdy folks, I have decided, after a discussion with Barand, to finally hang up the MySQLi shoes and move over to the dark side of PDO. I am trying to Update a profile, for example, but it is not working. No errors or anything. New to PDO so would love some help on figuring out where I am going wrong. Probably everywhere knowing me lol. Here is the dreaded code: if(isset($_POST['submit'])){ $id = trim($_SESSION['id']); //$trn_date = trim($db, date("Y-m-d H:i:s")); //$password = $db->real_escape_string(md5($_POST['password'])); $image = trim($_FILES['image']['name']); $name = trim($_POST['name']); $phone = trim($_POST['phone']); $email = trim($_POST['email']); $address = trim($_POST['address']); $license_number = trim($_POST['license_number']); $position = trim($_POST['position']); $role = trim($_POST['role']); $submittedby = trim($_SESSION["username"]); // image file directory $target = "images/".basename($image); if(!empty($_FILES['image']['name'])) { $sql = "UPDATE users SET name = :name, email = :email, phone = :phone, address = :address, license_number = :license_number, position = :position, role = :role, submittedby = :submittedby, image = :image"; }else{ $sql = "UPDATE users SET name = :name, email = :email, phone = :phone, address = :address, license_number = :license_number, position = :position, role = :role, submittedby = :submittedby"; } $stmt= $db->prepare($sql); if (move_uploaded_file($_FILES['image']['tmp_name'], $target)) { $msg = "Image uploaded successfully"; }else{ $msg = "Failed to upload image"; } if(!$stmt){ if ($stmt->execute()){ $message = ' <i class="fa fa-check text-danger"> Something went wrong please contact the server admin.</i>'; } else{ $message = ' <i class="fa fa-check text-success"> Record Updated!</i>'; } } } Any help folks would be appreciated

I have some sales data as below: SELECT bv.sale_time , amount_due , round(sum(amount_paid), 2) as paid , m.payment_method_id , m.method , bt.payment_status FROM basket_amount_due bv JOIN basket bt USING(basket_id) LEFT JOIN basket_payment p USING (basket_id) JOIN payment_method m USING(payment_method_id) GROUP BY bv.basket_id; +---------------------+------------+---------+-------------------+-------------------+----------------+ | sale_time | amount_due | paid | payment_method_id | method | payment_status | +---------------------+------------+---------+-------------------+-------------------+----------------+ | 2021-09-18 12:19:04 | 1170.00 | 1170.00 | 1 | CASH | paid | | 2021-09-18 12:19:39 | 756.60 | 0.00 | 1 | CASH | due | | 2021-09-18 12:20:22 | 1115.50 | 1000.00 | 1 | CASH | partial | | 2021-09-18 12:21:47 | 990.00 | 990.00 | 4 | Cash on Delivery | paid | | 2021-09-18 12:23:33 | 698.40 | 0.00 | 4 | Cash on Delivery | due | | 2021-09-18 12:29:45 | 2070.00 | 2070.00 | 2 | Credit/Debit Card | paid | +---------------------+------------+---------+-------------------+-------------------+----------------+ 6 rows in set (0.004 sec) My question is, Now I need to get total sales by payment method and payment status. That mean I want, total cash sales (= cash(paid) + Card (paid) + Cash on Delivery (paid)) total credit sales (= cash(due) + cash(partial) + Cash on Delivery (due)) total cash on delivery sales (= Cash on Delivery (paid) + Cash on Delivery (due)) total card sales This is the query I have so far.. SELECT DATE(bv.sale_time) , CASE p.payment_method_id WHEN (1 AND 2) THEN sum(amount_due) ELSE 0 END AS total_cash_sales , CASE p.payment_method_id WHEN 4 THEN sum(amount_due) ELSE 0 END AS total_credit_sales FROM basket_amount_due bv JOIN basket bt USING(basket_id) LEFT JOIN basket_payment p USING (basket_id) JOIN payment_method m USING(payment_method_id) WHERE DATE(bv.sale_time) >= CURDATE() AND DATE(bv.sale_time) < CURDATE() + INTERVAL 1 DAY GROUP BY bv.sale_time; Hope somebody may help me out to figure this out.

Hi, Phreaks, I'm stuck on something that maybe someone can help with on trying to send a user information to phpmyadmin I get this error -> There's 2 problems here, as you can see. 1) I've got an invalid datetime format. 2) is obviously the incorrect string value on my salt value. here is the corresponding code -> <?php if($validation->passed()) { $user = new User(); $salt = Hash::salt(32); try { $user->create(array( "username" => Input::get("usernane"), "password" => Hash::make(Input::get("password"), $salt), "email" => Input::get("email"), "salt" => $salt, "signup_date" => date_create('now')->format('Y-m-d H:i:s'), "role" => 2, )); $sent = true; } catch(Exception $e) { die($e->getMessage()); } } else { $errors = $validation->errors(); } It can be seen how I did both of them here. Additionally the Hash::salt method is here -> <?php public static function salt($length) { return random_bytes($length); } the column 'signup_date' is in my database as a datetime not null with 'none' as default. The column 'salt' is a VARCHAR(32) not null, 'none' as default Can anyone help me out with what I've done wrong, please. TIA

Hello, I have the following code that should select all strings that start with "rog" (like rogers, etc). SELECT user_id, first_name AS name, gender FROM user_details WHERE MATCH (first_name) AGAINST('rog*') The problme it's that shows me zero results. In first_name column I have a lot of words with "rogers". first_name column have Fulltext assign. If I use just "...AGAINST ('rogers')" it return corect data. It's something wrong with my sentence? Thank you

Hi, I am making a shopping cart and I have a problem. My code to remove the items from the cart works except for the first added item; I can delete all but that one. This is the form to remove the items. <form class="col-md-12" method="POST" action="" id="formremove"> <input type="hidden" name="id_prod" value="<?php echo $row['id_producto'] ?>"> <input type="submit" name="btn-remove" class="btn btn-info btn-sm" id="remove" value="X"> </form> And this is the php if ($_POST['btn-remove']) { $id_user=$_SESSION['id_user']; $id_prod = $_POST['id_prod']; $SQLREM = "DELETE FROM `carrito` WHERE id_user='$id_user' and id_producto='$id_prod';"; if (mysqli_query($conn, $SQLREM)) { echo '<script> alert("Ok"); window.location="/CBA/WAUW/cart.php"</script>'; } else { echo '<script> alert("Error"); window.location="/CBA/WAUW/cart.php"</script>'; }; } The connection to the database works fine; as I said before, it deletes everything but the first item added. I would be very grateful for any help.

Data from category table: +-------------+-----------+---------------------+-------------+ | category_id | parent_id | name | description | +-------------+-----------+---------------------+-------------+ | 1 | NULL | Products | NULL | | 2 | 1 | Computers | NULL | | 3 | 2 | Laptops | NULL | | 4 | 2 | Desktop Computers | NULL | | 5 | 2 | Tab PCs | NULL | | 6 | 2 | CRT Monitors | NULL | | 7 | 2 | LCD Monitors | NULL | | 8 | 2 | LED Monitors | NULL | | 9 | 1 | Mobile Phones | NULL | | 10 | 9 | LG Phone | NULL | | 11 | 9 | Anroid Phone | NULL | | 12 | 9 | Windows Mobile | NULL | | 13 | 9 | iPad | NULL | | 14 | 9 | Samsung Galaxy | NULL | | 15 | 1 | Digital Cameras | NULL | | 16 | 1 | Printers and Toners | NULL | | 17 | 14 | Galaxy S Series | NULL | | 18 | 14 | Galaxy Note Series | NULL | | 19 | 14 | Galaxy Z Fold2 5G | NULL | | 20 | 17 | Phone 1 | NULL | | 21 | 17 | Phone 2 | NULL | +-------------+-----------+---------------------+-------------+ Just think, I hava an array of category ids for delete like this: ids = [9,17,20]; Now I want to delete the category related to the above array and update the relevant child category. According to this example, the parent_id of the category_id 10,11,12,13,14 should be 1 The parent_id in category 21 should be updated to 14. In another case, suppose I delete category 9, 18 then all the relevant sub and sub sub categories should be updated as parant category. I hope somebody may help me out. Thank you

Hi All, I have the following, and when i run it the ifnull() is returning null rather than 0 as shown in the attached. Any help on this would be greatly appreciated. select * from ( SELECT ptsl_ptd_id, SUBSTRING(ptsl_date,1,10) as ptsl_date, ptsl_z_id, z_rfid, ptsl_limit FROM `prs_ptsl` inner join prs_z on ptsl_z_id=z_id where ptsl_ptd_id='7' ) as limits left join ( SELECT pr_ptd_id, za_sdate, za_z_id, za_z_rfid, IFNULL(count(za_pr_id), 0) as used FROM `prs_za` inner join prs_pr on za_pr_id=pr_id where prs_pr.pr_status = 'Approved' or prs_pr.pr_status = 'Submitted' group by za_sdate, za_z_id, za_z_rfid ) as used on limits.ptsl_ptd_id=used.pr_ptd_id and limits.ptsl_date=used.za_sdate and ptsl_z_id=za_z_id where ptsl_date = '2021-06-12' and (ptsl_limit - IFNULL(used, 0) >= 0) limit 100

Hello everyone. I'm a self learner that is very new to programming. Three tables are given: table `worker` (worker) with data - id (worker id), first_name (name), last_name (last name) table `child` (child) with data - worker_id (worker id), name (child name) table `car` (machine) with data - worker_id (worker id), model (car model) Table structure: CREATE TABLE `worker` ( `id` int(11) NOT NULL, `first_name` varchar(100) NOT NULL, `last_name` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `car` ( `user_id` int(11) NOT NULL, `model` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `child` ( `user_id` int(11) NOT NULL, `name` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; It is necessary to write one SQL query that returns: names and surnames of all employees, a list of their children separated by commas and a car brand. You need to select only those workers who have or had a car (if there was a car and then it was gone, then the model field becomes null).

//my controller <?php namespace App\Http\Controllers; use Illuminate\Http\Request; use DB; class homeController extends Controller { public function index() { $employee = DB::table('employee')->orderBy('id','desc')->get(); $department = DB::table('department')->orderBy('id','desc')->get(); return view('index', ['employee' => $employee , 'department' => $department]); } } //my routes Route::get('index','homeController@index'); //my view using blade temmplating engine @foreach($employee as $emp) <div class="employee"> <b>{{ $emp->name }} </b> <a href="employee/{{ $emp->id }}"> <p class="intro">{{ substr($emp->intro ,0, 50) }}...</p> </a> </div> @endforeach @foreach($department as $dep) <div class="department"> <b>{{ $dep->name }} </b> <a href="department/{{ $dep->id }}"> <p class="desc">{{ substr($dep->description ,0, 100) }}...</p> </a> </div> @endforeach I want to fetch using ajax, how can i do it, teach/help me

Good day I have three tables - receiving - shipping and stock movement. everyday i transfer into stock movent the sum of receining with date - havein a stock movent entry per day - I also at end of day update with shipping for that day, in stock table I calculate ne stock level. my problem is that when three days bac i stil have stock i need to start subtracting from oldest entry first till it reaches 0 the move over to second eldest? i have no idea where to start or how to achieve this

Hi - I want to add to a php form a button which will open a pop-up window with records (names of people) and associated radio buttons. On click on respective name's radio button and SUBMIT, parent form textbox is populated. (list of names will be dynamically be selected fro MySQL table). Any suggestions? Many thanks! IB.

I wrote this really nice posting system for a site I'm working on. Problem is, I messed it up somehow, and now I can retrieve $_POST variables so I can post stuff to a MySQL database. I'm really new to PHP, and I have no idea what I did wrong. HTML code: <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <meta name="description" content="The PPC Planet software archive."> <meta name="author" content="JohnS and VP44"> <title>PPC Planet Public Archive</title> <link rel="canonical" href="https://getbootstrap.comhttps://getbootstrap.com/docs/4.5/examples/jumbotron/"> <!-- Bootstrap core CSS --> <link href="https://getbootstrap.com/docs/4.5/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous"> <!-- Favicons --> <link rel="apple-touch-icon" href="images/ppc.png" sizes="180x180"> <link rel="icon" href="images/ppc.png" sizes="32x32" type="image/png"> <link rel="icon" href="images/ppc.png" sizes="16x16" type="image/png"> <meta name="theme-color" content="#28A745"> <style> .bd-placeholder-img { font-size: 1.125rem; text-anchor: middle; -webkit-user-select: none; -moz-user-select: none; -ms-user-select: none; user-select: none; } @media (min-width: 768px) { .bd-placeholder-img-lg { font-size: 3.5rem; } } .cover { background-image: url("images/earth.jpg"); background-size: cover; background-color: rgba(0, 0, 0, .8); background-blend-mode: multiply; } </style> <link href="stylesheets/2kstyle.css" rel="stylesheet" type="text/css"> <link href="stylesheets/archivestyle.css" rel="stylesheet" type="text/css"> <link href="stylesheets/posts.css" rel="stylesheet" type="text/css"> </head> <body style="background-color: black; color: white;"> <nav class="navbar navbar-dark fixed-top green"> <a class="navbar-brand" href="index.html"><b>PPC</b>Planet</a> <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarsExample09" aria-controls="navbarsExample09" aria-expanded="false" aria-label="Toggle navigation"> <span class="navbar-toggler-icon"></span> </button> <div class="collapse navbar-collapse" id="navbarsExample09"> <ul class="navbar-nav mr-auto "> <li class="nav-item"> <a class="nav-link" href="index.html">Home</span></a> </li> <li class="nav-item active"> <a class="nav-link" href="archive.html">Archive <span class="sr-only">(current)</a> </li> <li class="nav-item"> <a class="nav-link" href="news.html">News</a> </li> <li class="nav-item"> <a class="nav-link" href="contact.html">Contact</a> </li> <li class="nav-item"> <a class="nav-link" href="about.html">About</a> </li> </ul> </div> </nav> <br><br><br><br> <script src="https://www.google.com/recaptcha/api.js"></script> <div class="content home"> <h2 style="color: white;"><b>PPC Planet Public Archive</b></h2> <br> <div id="backDiv"> <a href="deletepost.php"><b>(🗑) Delete or (🚩) report a post</b></a> <br><br> <button id="backDiv" class="greenBtn" onclick="back()">« back</button> <br><br><br> </div> <div id="postsDiv" class="posts content home"></div> <div id="captcha"> <p>To prevent spam and unwanted submissions, we require that you complete the CAPTCHA below.</p> <br> <div class="g-recaptcha brochure__form__captcha" data-sitekey="6Ldku8QZAAAAABQJVhyfOnVljIoUoihUuBUfaFJn" required></div> <br><br><br> <input type="checkbox" id="findCheck" onchange="findToggle()"> <label for="findCheck">Filter Listings</label> <br> <div style="display: none;" id="searchDiv"> <!--text input--> <input type="radio" id="textsearch" name="filters" value="textsearch"> <label for="textsearch">Search by text</label> &nbsp;&nbsp;&nbsp; <input style="width: 75%;" placeholder="Show results that contain inputted text..." type="text" id="searchTxt" /> <br><br> <!--type picker--> <input type="radio" id="typesearch" name="filters" value="typesearch"> <label for="typesearch">Search by type</label> &nbsp;&nbsp;&nbsp; <select name="typeselect" id="typeselect"> <option value="freeware">Freeware</option> <option value="abandonware">Abandonware</option> <option value="self-made">I wrote it myself</option> </select> <br><br> <!--category picker--> <input type="radio" id="categorysearch" name="filters" value="categorysearch"> <label for="categorysearch">Search by category</label> &nbsp;&nbsp;&nbsp; <select name="categoryselect" id="categoryselect"> <option value="app">App</option> <option value="game">Game</option> <option value="driver">Driver</option> <option value="manual">Manual</option> <option value="setup">Setup</option> <option value="ROM">ROM</option> <option value="other">Other</option> </select> </div> <br><br> <button class="greenBtn" onclick="callValidation()">Visit Archive</button> </div> </div> <br><br><br><br> <script> document.getElementById("postsDiv").style.display = "none"; document.getElementById("captcha").style.display = "block"; document.getElementById("searchDiv").style.display = "none"; document.getElementById("backDiv").style.display = "none"; function callValidation() { if (grecaptcha.getResponse().length == 0) { //if CAPTCHA not complete alert('Please complete the CAPTCHA.'); } else { //reset reCAPTCHA and show + hide stuff grecaptcha.reset() document.getElementById("postsDiv").style.display = "block"; document.getElementById("backDiv").style.display = "block"; document.getElementById("captcha").style.display = "none"; //show posts if (document.getElementById("findCheck").checked == true && document.getElementById("typesearch").checked == true) { document.getElementById("searchTxt").value = document.getElementById("typeselect").value; } else if (document.getElementById("findCheck").checked == true && document.getElementById("categorysearch").checked == true) { document.getElementById("searchTxt").value = document.getElementById("categoryselect").value; } //fetch posts from database var posts_search_query = document.getElementById("searchTxt").value; fetch("posts.php?search_query=" + posts_search_query).then(response => response.text()).then(data => { document.querySelector(".posts").innerHTML = data; document.querySelectorAll(".posts .write_post_btn, .posts .reply_post_btn").forEach(element => { element.onclick = event => { event.preventDefault(); document.querySelectorAll(".posts .write_post").forEach(element => element.style.display = 'none'); document.querySelector("div[data-post-id='" + element.getAttribute("data-post-id") + "']").style.display = 'block'; document.querySelector("div[data-post-id='" + element.getAttribute("data-post-id") + "'] input[name='name']").focus(); }; }); document.querySelectorAll(".posts .write_post form").forEach(element => { element.onsubmit = event => { event.preventDefault(); fetch("posts.php?search_query=" + posts_search_query, { method: 'POST', body: new FormData(element) }).then(response => response.text()).then(data => { element.parentElement.innerHTML = data; }); }; }); }); } } function back() { document.getElementById("backDiv").style.display = "none"; document.getElementById("postsDiv").style.display = "none"; document.getElementById("captcha").style.display = "block"; document.getElementById("searchTxt").value = ""; } //when filter toggle changed function findToggle() { if (document.getElementById("findCheck").checked == true) { //when checked document.getElementById("searchDiv").style.display = "block"; document.getElementById("searchTxt").style.display = "block"; document.getElementById("categoryselect").style.display = "block"; document.getElementById("typeselect").style.display = "block"; document.getElementById("textsearch").checked = true; } else { //when unchecked document.getElementById("searchDiv").style.display = "none"; } } </script> <footer class="container center white "> <p>&copy; PPC Planet Team 2020</p> <br> </footer> <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js " integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj " crossorigin="anonymous "></script> <script> window.jQuery || document.write('<script src="https://getbootstrap.com/docs/4.5/assets/js/vendor/jquery.slim.min.js "><\/script>') </script> <script src="https://getbootstrap.com/docs/4.5/dist/js/bootstrap.bundle.min.js " integrity="sha384-LtrjvnR4Twt/qOuYxE721u19sVFLVSA4hf/rRt6PrZTmiPltdZcI7q7PXQBYTKyf " crossorigin="anonymous "></script> </body> </html> PHP code: <?php include('mysqlconnect.php'); error_reporting(E_ALL); try { $pdo = new PDO('mysql:host=' . $DATABASE_HOST . ';dbname=' . $DATABASE_NAME . ';charset=utf8', $DATABASE_USER, $DATABASE_PASS); } catch (PDOException $exception) { // If there is an error with the connection, stop the script and display the error exit('Failed to connect to database!' . $exception); } // Below function will convert datetime to time elapsed string function time_elapsed_string($datetime, $full = false) { $now = new DateTime; $ago = new DateTime($datetime); $diff = $now->diff($ago); $diff->w = floor($diff->d / 7); $diff->d -= $diff->w * 7; $string = array('y' => 'year', 'm' => 'month', 'w' => 'week', 'd' => 'day', 'h' => 'hour', 'i' => 'minute', 's' => 'second'); foreach ($string as $k => &$v) { if ($diff->$k) { $v = $diff->$k . ' ' . $v . ($diff->$k > 1 ? 's' : ''); } else { unset($string[$k]); } } if (!$full) $string = array_slice($string, 0, 1); return $string ? implode(', ', $string) . ' ago' : 'just now'; } // This function will populate the posts and posts replies using a loop function show_posts($posts, $parent_id = -1) { $html = ''; if ($parent_id != -1) { // If the posts are replies sort them by the "submit_date" column array_multisort(array_column($posts, 'submit_date'), SORT_ASC, $posts); } $resultCount = 0; // Iterate the posts using the foreach loop foreach ($posts as $post) { if (($_GET['search_query']) != "") { if ($post['parent_id'] == $parent_id) { if (strpos(implode($post), $_GET['search_query'])) { $resultCount++; //check if optional variables are not set $screenshot = $post['screenshot']; if ($screenshot.trim() == "") { $screenshot = "https://ppcplanet.org/images/noscreenshot.png"; } $serial = $post['serial']; if ($serial.trim() == "") { $serial = "n/a"; } $source = $post['source']; if ($source.trim() == "") { $source = "n/a"; } $html .= ' <div class="post"> <br><br> <div> <h3 style="color: white;" class="name"><b>By ' . htmlspecialchars($post['postauthor'], ENT_QUOTES) . '</b></h3> <span class="date">' . time_elapsed_string($post['submit_date']) . '</span> </div> <br> <img class="image" style="width: 256px; height: 256px; overflow: hidden; object-fit: cover;" src=' . nl2br(htmlspecialchars($screenshot, ENT_QUOTES)) . ' alt="Screenshot"/> <br><br> <h2 class="content"><b><a href=' . nl2br(htmlspecialchars($post['url'], ENT_QUOTES)) . ' target="_blank">' . nl2br(htmlspecialchars($post['name'], ENT_QUOTES)) . '</a></b></h2> <br> <p class="content"><b>Description: </b>' . nl2br(htmlspecialchars($post['content'], ENT_QUOTES)) . '</p> <p class="content"><b>Serial: </b>' . nl2br(htmlspecialchars($serial, ENT_QUOTES)) . ' </p> <p class="content"><b>Original Source: </b> <a href =' . nl2br(htmlspecialchars($source, ENT_QUOTES)) . ' target="_blank">' . nl2br(htmlspecialchars($post['source'], ENT_QUOTES)) .'</a></p> <p class="content"><b>Type: </b>' . nl2br(htmlspecialchars($post['type'], ENT_QUOTES)) . ' </p> <p class="content"><b>Category: </b>' . nl2br(htmlspecialchars($post['category'], ENT_QUOTES)) . ' </p> <a class="reply_post_btn" href="#" data-post-id="' . $post['id'] . '">Add on... (ex. another version, manual, etc.)</a> ' . show_write_post_form($post['id']) . ' <div class="replies"> ' . show_posts($posts, $post['id']) . ' </div> </div> <br><br><br> '; ob_clean(); echo(strval($resultCount) . ' result(s) found for "' . $_GET['search_query'] . '"'); //display number of results } } } else { //add each post to HTML variable if ($post['parent_id'] == $parent_id) { //check if optional variables are not set $screenshot = $post['screenshot']; if ($screenshot.trim() == "") { $screenshot = "https://ppcplanet.org/images/noscreenshot.png"; } $serial = $post['serial']; if ($serial.trim() == "") { $serial = "n/a"; } $source = $post['source']; if ($source.trim() == "") { $source = "n/a"; } $html .= ' <div class="post"> <h2></h2> <br><br> <div> <h3 style="color: white;" class="name"><b>By ' . htmlspecialchars($post['postauthor'], ENT_QUOTES) . '</b></h3> <span class="date">' . time_elapsed_string($post['submit_date']) . '</span> </div> <br> <img class="image" style="width: 256px; height: 256px; overflow: hidden; object-fit: cover;" src=' . nl2br(htmlspecialchars($screenshot, ENT_QUOTES)) . ' alt="Screenshot"/> <br><br> <h2 class="content"><b><a href=' . nl2br(htmlspecialchars($post['url'], ENT_QUOTES)) . ' target="_blank">' . nl2br(htmlspecialchars($post['name'], ENT_QUOTES)) . '</a></b></h2> <br> <p class="content"><b>Description: </b>' . nl2br(htmlspecialchars($post['content'], ENT_QUOTES)) . '</p> <p class="content"><b>Serial: </b>' . nl2br(htmlspecialchars($serial, ENT_QUOTES)) . ' </p> <p class="content"><b>Original Source: </b> <a href =' . nl2br(htmlspecialchars($source, ENT_QUOTES)) . ' target="_blank">' . nl2br(htmlspecialchars($post['source'], ENT_QUOTES)) .'</a></p> <p class="content"><b>Type: </b>' . nl2br(htmlspecialchars($post['type'], ENT_QUOTES)) . ' </p> <p class="content"><b>Category: </b>' . nl2br(htmlspecialchars($post['category'], ENT_QUOTES)) . ' </p> <a class="reply_post_btn" href="#" data-post-id="' . $post['id'] . '">Add on... (ex. another version, manual, etc.)</a> ' . show_write_post_form($post['id']) . ' <div class="replies"> ' . show_posts($posts, $post['id']) . ' </div> </div> <br><br><br> '; } } } return $html; } // This function is the template for the write post form function show_write_post_form($parent_id = -1) { $rand = randomIdentifier(); //generate random identifier string $html = ' <div class="write_post" data-post-id="' . $parent_id . '"> <form method="post"> <h2 style="color: white;">New Post</h2> <br> <input name="parent_id" type="hidden" value="' . $parent_id . '"> <label for="name">Title:</label> <input style="width: 100%;" id="name" name="name" type="text" placeholder="Enter a title..." required> <br><br> <label for="screenshot">Screenshot (if applicable):</label> <input style="width: 100%;" id="screenshot" name="screenshot" type="url" placeholder="Screenshot URL"> <br><br> <label for="type">URL:</label> <input style="width: 100%;" id="url" name="url" type="url" placeholder="Download URL" required> <br><br> <label for="type">Description:</label> <textarea name="content" id="content" placeholder="Write a description..." required></textarea> <br><br> <label for="type">Original Source (if known):</label> <input style="width: 100%;" id="source" name="source" type="url" placeholder="Original Source URL"> <br><br> <label for="type">Serial (if applicable):</label> <input style="width: 100%;" id="serial" name="serial" type="text" placeholder="Serial"> <br><br> <label for="name">Your Name/Nickname:</label> <input style="width: 100%;" id="postauthor" name="postauthor" type="text" placeholder="Enter your name..." required> <br><br> <br> <label for="type">Choose a type:</label> <select name="type" id="type"> <option value="freeware">Freeware</option> <option value="abandonware">Abandonware</option> <option value="self-made">I wrote it myself</option> </select> &nbsp;&nbsp;&nbsp; <label for="category">Category:</label> <select name="category" id="category"> <option value="app">App</option> <option value="game">Game</option> <option value="driver">Driver</option> <option value="manual">Manual</option> <option value="setup">Setup</option> <option value="ROM">ROM</option> <option value="other">Other</option> </select> <br><br> <h2 style="color: white;">Post identifier string</h2> <input name="identifier" id="identifier" style="width: 100%;" readonly="true" type="text"" value="' . $rand . '"> <br> <p style="color: red;">This is your post identifier string. It can be used to delete this post in the future without having to contact an admin. <b>Make sure you do not lose it!</b></p> <br><br> <h2 style="color: white;">Make sure your submission meets the following criteria:</h2> <br> <p>🙂 This submission is appropriate and doesn\'t have any mature content. - We want PPC Planet to be a safe place for people of all ages. Inappropriate submissions will be removed!</p> <p>👍 This submission is either freeware, abandonware, or self-made. - No piracy! It\'s not fair to the developer(s).</p> <p>💻 This submission has been tested, and works as advertised. - We don\'t want to have a bunch of broken software on the archive.</p> <p>🧾 This submission is not already on the archive. - Be sure that you are posting something unique!</p> <p>📱 This submission is related to Pocket PCs. - Remember, this is an archive of Pocket PC software.</p> <br> <p><b>By following these rules, we can make the archive a fun (and totally rad) place for everyone!</b></p> <br><br> <p style="color: red; font-size: xx-large; "><b>Make sure you have proofread your post, as you will not be able to edit it once it has been posted. Additionally, make sure you write your down identifier string somewhere if you have not already.</b></p> <br><br> <button type="submit">Create Post</button> <br><br> </form> </div> '; return $html; } if (isset($_GET['search_query'])) { // Check if the submitted form variables exist if (isset($_POST['name'])) { $stmt = $pdo->prepare('INSERT INTO posts (page_id, parent_id, name, screenshot, url, content, serial, type, category, identifier, source, postauthor, submit_date) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,NOW())'); $stmt->execute([ 1, $_POST['parent_id'], $_POST['name'], $_POST['screenshot'], $_POST['url'], $_POST['content'], $_POST['serial'], $_POST['type'], $_POST['category'], $_POST["identifier"], $_POST["source"], $_POST["postauthor"] ]); exit('Your post has been submitted! You can reload the page to see it.'); } // Get all posts by the Page ID ordered by the submit date $stmt = $pdo->prepare('SELECT * FROM posts WHERE page_id = ? ORDER BY submit_date DESC'); $stmt->execute([ 1 ]); $posts = $stmt->fetchAll(PDO::FETCH_ASSOC); // Get the total number of posts $stmt = $pdo->prepare('SELECT COUNT(*) AS total_posts FROM posts WHERE page_id = ?'); $stmt->execute([ 1 ]); $posts_info = $stmt->fetch(PDO::FETCH_ASSOC); } else { exit('No search query specified!'); } function randomIdentifier() { $pass = 0; $complete = false; while (!$complete) { //generate random identifier string until it is unique $alphabet = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*()'; $pass = array(); $alphaLength = strlen($alphabet) - 1; for ($i = 0; $i < 100; $i++) { $n = rand(0, $alphaLength); $pass[] = $alphabet[$n]; } include('mysqlconnect.php'); $pdo = new PDO('mysql:host=' . $DATABASE_HOST . ';dbname=' . $DATABASE_NAME . ';charset=utf8', $DATABASE_USER, $DATABASE_PASS); $data = implode($pass); $stmt = $pdo->prepare( "SELECT identifier FROM posts WHERE identifier =:id" ); $stmt->bindParam(':id', $data, PDO::PARAM_STR); $stmt->execute(); $myIdentifier = $stmt->fetch(); if (!$myIdentifier) { //identifier is unique $complete = true; } } return $data; } ?> <div class="post_header"> <span style="color: white;" class="total"><?=$posts_info['total_posts']?> total post(s)</span> <a style="color: white;" href="#" class="write_post_btn" data-post-id="-1">Create Post</a> </div> <?=show_write_post_form()?> <?=show_posts($posts)?> How can I fix this so posting works again? All help is appreciated!