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.

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.

Need some pointers. User (usr), Application (app), and Permission (acs) tables. Need an SQL to ascertain access levels for read, modify, and admin.. A record in asc is NOT guaranteed! in the app table for example if ap_read = 1 then we need not a record in acs to read whatever is there. If however it was 0 we would need a record in asc with read = 1 for that app/usr combo I currently open and query these three separately, and it works, but there has to be a more elegant way. not EXACTLY normalized.. I guess. I hope the question is descriptive enough.

Having a problem with a query I am trying to improve with a Join. Here it is: $q = "SELECT a.wine_num, a.activity, date_format(a.date_posted,'%m-%d-%y') as date_mdy, d.activity_desc, a.bottle_cnt, a.my_comments, w.label, w.vintage, CONCAT(s.sort_date, a.wine_num) as sort_col FROM mywines_activity a, mywines_activity_codes d, mywines w LEFT OUTER JOIN (SELECT wine_num, max(date_posted) as sort_date FROM mywines_activity GROUP BY wine_num) s ON s.wine_num = a.wine_num WHERE $sel_wine a.activity = d.activity_code and a.wine_num = w.wine_num ORDER BY sort_col DESC"; The error is "a.wine_num" is not a column name. But - it is! It is actually one of the keys of table a and it is being using in several relationships here. The whole query works great without the join. I'm just to trying to add something to better sequence it.

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.

files that upload during insert/submit form was gone , only files upload during the update remain , is the way query for update multiple files is wrong ? $targetDir1= "folder/pda-semakan/ic/"; if(isset($_FILES['ic'])){ $fileName1 = $_FILES['ic']['name']; $targetFilePath1 = $targetDir1 . $fileName1; //$main_tmp2 = $_FILES['ic']['tmp_name']; $move2 =move_uploaded_file($_FILES["ic"]["tmp_name"], $targetFilePath1); } $targetDir2= "folder/pda-semakan/sijil_lahir/"; if(isset($_FILES['sijilkelahiran'])){ $fileName2 = $_FILES['sijilkelahiran']['name']; $targetFilePath2 = $targetDir2 . $fileName2; $move3 =move_uploaded_file($_FILES["sijilkelahiran"]["tmp_name"], $targetFilePath2); } $targetDir3= "folder/pda-semakan/sijil_spm/"; if(isset($_FILES['sijilspm'])){ $fileName3 = $_FILES['sijilspm']['name']; $targetFilePath3 = $targetDir3 . $fileName3; $move4 =move_uploaded_file($_FILES["sijilspm"]["tmp_name"], $targetFilePath3); } $query1=("UPDATE semakan_dokumen set student_id='$noMatrik', email= '$stdEmail', surat_tawaran='$fileName', ic='$fileName1',sijil_lahir='$fileName2',sijil_spm= '$fileName3' where email= '$stdEmail'");

I have this code that i tried running it on my phpmyadmin it works fine ,but when i use it on my php code to query its not working.What could be the issue? SELECT * FROM `roombook` ORDER BY (CASE stat WHEN 'Checked in' THEN 1 WHEN 'Booked' THEN 2 WHEN 'Deposit Confirmation' THEN 3 WHEN 'Email/phone' THEN 4 WHEN 'Checked Out' THEN 5 ELSE 'Cancelled' END) ASC, stat ASC

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).

What is the best table structure when constructing what will be a potentially large multi-user platform? Example- Each user inputs their own individualized information into a table, for recall only to that specific user and to certain other users defined as administrators or half-administrators super users. Would it be better to store this all in a single table, or to give each user their own individual table on formation of the account?

Good morning. I'm doing this project but I'm stuck when the app need to match the dropdown result with the scan result of qr code. What should I do? Thanks in advance to everyone. <!doctype html> <html dir=ltr style="overflow-x: hidden;padding: 0px;width: 100%;"> <head> <meta charset=utf-8> <meta http-equiv=X-UA-Compatible content="IE=edge"> <meta name=viewport content="width=device-width, initial-scale=1"> <title></title> <meta name="description" content=""> <meta name="author" content="SoftMat"> <link media="all" href="css/style.css" rel="stylesheet" /> <!-- qrcode-reader core CSS file --> <link rel="stylesheet" href="css/qrcode-reader.min.css"> <!-- jQuery --> <script src="js/jquery.min.js"></script> <!-- qrcode-reader core JS file --> <script src="js/qrcode-reader.min.js"></script> <script> $(function(){ // overriding path of JS script and audio $.qrCodeReader.jsQRpath = "js/jsQR.min.js"; $.qrCodeReader.beepPath = "audio/sound.mp3"; // bind all elements of a given class $(".qrcode-reader").qrCodeReader(); // bind elements by ID with specific options $("#openreader-multi2").qrCodeReader({multiple: true, target: "#multiple2", skipDuplicates: false}); $("#openreader-multi3").qrCodeReader({multiple: true, target: "#multiple3"}); // read or follow qrcode depending on the content of the target input $("#openreader-single2").qrCodeReader({callback: function(code) { if (code) { window.location.href = code; } }}).off("click.qrCodeReader").on("click", function(){ var qrcode = $("#single2").val().trim(); if (qrcode) { window.location.href = qrcode; } else { $.qrCodeReader.instance.open.call(this); } }); }); </script> </head> <body> <div align="center" class="container"> <div align="center" class="col-xs-12"> <div align="center" class="container" style="background-color:white; box-shadow:0px 2px #00000085;"> <img style="margin-top:15px; margin-bottom:15px;" src="img/logo.png"> </div> <div align="center" class="col-xs-12" style="background-image: url(img/blakcstonemain.png); background-repeat:no-repeat; background-position:center; background-size: cover; margin-top:10px;"> <br> <!--Lavorazione : Scelta OP--> <h1 style="margin-bottom: 0px;">Seleziona Ordine di Produzione</h1> <?php $conn = odbc_connect('', '', ''); if(! $conn){ print( "..." ); exit; } //definisco gli ordini di produzione $sql="SELECT * FROM dbo.OP_Ordini LEFT JOIN dbo.MG_AnaART ON dbo.OP_Ordini.OPOR_MGAA_Id = dbo.MG_AnaArt.MGAA_Id "; $rs=odbc_exec($conn,$sql); if (!$rs) {exit("Errore nella tabella!");} echo"<center>"; echo"<br>"; echo"<select>"; echo"<option>--ORDINI--</option>"; while(odbc_fetch_row($rs)) { $ord_id=odbc_result($rs,"OPOR_Id"); $ord_anno=odbc_result($rs,"OPOR_Anno"); $ord_ordine=odbc_result($rs,"OPOR_Ordine"); $ord_lotto=odbc_result($rs,"OPOR_Lotto"); $ord_desc=odbc_result($rs,"OPOR_Descr"); $mgaa_matr=odbc_result($rs,"MGAA_Matricola"); echo"<option>| $ord_id | $ord_anno | $ord_ordine | $ord_lotto | $ord_desc | $mgaa_matr</option>"; } echo"</select>"; echo"<br>"; echo"<br>"; ?> <!--Lavorazione : Scansione--> <div align="center" class="col-xs-12"> <h1 style="margin-bottom: 0px;">Scansione Materiale</h1> <br> <label for="single"></label> <input id="single" type="text" size="50"> <button type="button" class="qrcode-reader" id="openreader-single" data-qrr-target="#single" data-qrr-audio-feedback="false" data-qrr-qrcode-regexp="^https?:\/\/"><img style="width:20%;" src="img/qr1.png"></button> <!--Lavorazione : Matching--> <h1 style="margin-bottom: 0px;">Esamina</h1> <img style="width:20%;" src="img/compara.png"> <input id="submit" type="submit" value="MANDALA!"> <!--<video id="preview" class="p-1 border" style="width:75%;border: solid #d34836;box-shadow: 5px 5px #000000a6;"></video>--> </div> </div> </div> </div> </body> </html>

My login script stores the user's login name as $_SESSION[ 'name'] on login. For some unapparent reason, i'm getting errors stating that $user and $priv are undefined variables, though I've attempted to define $user as being equal to $_SESSION['name'], using $user to look up the the user's privilege level (stored as the su column ) in the SQL table, and then where the result of the sql query is $priv which is then evaluated in an if statement. I can't seem to figure out why this might not be working. The code I'm using: <?php session_start(); function verify() { //verify that the user is logged in via the login page. Session_start has already been called. if (!isset($_SESSION['loggedin'])) { header('Location: /index.html'); exit; } //if user is logged in, we then lookup necessary privleges. $_SESSION['name'] was written with the login name upon login. Privleges // are written in db as a single-digit integer of of 0 for users, 1 for administrators, and 2 for special users. $user === $_SESSION['name']; //Connect to Databse $link = mysqli_connect("127.0.0.1", "database user", "password", "database"); if (!$link) { echo "Error: Unable to connect to MySQL." . PHP_EOL; echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL; echo "Debugging error: " . mysqli_connect_error() . PHP_EOL; exit; } //SQL Statement to lookup privlege information. if ($result = mysqli_query($link, "SELECT su FROM accounts WHERE username = $user", MYSQLI_STORE_RESULT)) { //LOOP TO CYCLE THROUGH SQL RESULTS AND STORE Privlege information as vairable $priv. while ($row = $result->fetch_assoc()) { $priv === $row["su"]; } } // close SQL connection. mysqli_close($link); // Verify privleges and take action. Only a privlege of "1" is allowed to view this page. A privlege of "2" indicates special //accounts used in other scripts that have certain indermediate additional functions, but are not trusted administrators. if ($priv !== 1) { echo $_SESSION['name']; echo "you have privlege level of $priv"; echo "<br>"; echo 'Your account does not have the privleges necessary to view this page'; exit; } } verify(); ?>

I am trying to add a bootstrap class to php echo in mysql query but it doesn't work Here the code that I using $result = $conn->query($sql); echo ""; echo " New Users "; echo " "; echo ""; Any ides ?

I have this at the top of my index.php: <?php session_start(); // start of script every time. // setup a path for all of your canned php scripts $php_scripts = '/home/larry/web/test/php/'; // a folder above the web accessible tree // load the pdo connection module require $php_scripts . 'PDO_Connection_Select.php'; require $php_scripts . 'GetUserIpAddr.php'; //******************************* // Begin the script here $ip = GetUserIpAddr(); if (!$pdo = PDOConnect("foxclone")): { echo "Failed to connect to database" ; exit; } else: { $stmt = $pdo->prepare("INSERT INTO 'download' ('IP_ADDRESS', 'FILENAME') VALUES (?, ?"); $stmt->bindParam(1, $ip); $stmt->bindParam(2, $filename); $stmt->execute(); } endif; //exit(); ?> I'm getting the following error at the $pdo->prepare line: Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''download' ('IP_ADDRESS','FILENAME') VALUES (?, ?' at line 1 in /home/larry/web/test/public_html/index2.php:23 Stack trace: #0 /home/larry/web/test/public_html/index2.php(23): PDO->prepare('INSERT INTO 'do...') #1 {main} thrown in /home/larry/web/test/public_html/index2.php on line 23 I verified the format of the statement at https://www.w3schools.com/php/php_mysql_prepared_statements.asp but am unsure if it needs to be in the PDO_Connection_Select.php, or it belongs where I have it since the db is already connected.

//multi query $sql= "SELECT option_value FROM settings WHERE option_name = 'email_temp_issue';"; //1 $sql .="SELECT title FROM books WHERE id = '$bookid';"; //2 $sql .="SELECT fullName from students WHERE email = '$username'"; //3 $sql_run = mysqli_multi_query($connection,$sql); if(mysqli_num_rows($sql_run)>0) { foreach ($sql_run as $row) { $email_template = $row['option_value']; //retrieve from query 1 $title = $row['title']; //retrieve from query 2 $name = $row['fullName']; //retrieve from query 3 } } I am trying to store the data from the multi query into each variable, which I am not sure how to do..Anyone can help? :)

Hello everyone, I am a total newbie in PHP and so do here. I am trying to build a table consisting foreign key and primary key and it is showing error like "Parse error: syntax error, unexpected 'cities' (T_STRING) in line number 29". I have marked up my lines according to sublime text 3 editor. I am trying to create a database. 1 <?php 2 $servername = "localhost"; 3 $username = "username"; 4 $password = "password"; 5 $dbname = "newDB"; 7 // Create connection 8 $conn = new mysqli($servername, $username, $password, $dbname); 10 // Check connection 11 if ($conn->connect_error) { 12 die("Connection failed: " . $conn->connect_error);} 14 $firstname = $conn->real_escape_string($_REQUEST['firstname']); 15 $lastname = $conn->real_escape_string($_REQUEST['lastname']); 16 $address = $conn->real_escape_string($_REQUEST['address']); 17 $city = $conn->real_escape_string($_REQUEST['city']); 18 $country = $conn->real_escape_string($_REQUEST['country']); 19 $phone_number = $conn->real_escape_string($_REQUEST['phone_number']); 20 $email = $conn->real_escape_string($_REQUEST['email']); 22 // Attempt insert query execution 23 $sql1 = "INSERT INTO cities VALUES ('$city')"; 25 $sql2 = "INSERT INTO countries VALUES ('$country')"; 27 $sql3 = "INSERT INTO Contacts (firstname, lastname, address, city, country, phone, email) VALUES ('$firstname', '$lastname', '$address', $city, $country, '$phone_number','$email')"; 29 SELECT * FROM cities; SELECT * FROM countries; SELECT * FROM Contacts; if($conn->query($sql1) === true){ echo "City added successfully."; } else{ echo "ERROR: Could not able to execute " . $conn->error; } if($conn->query($sql2) === true){ echo "Çountry added successfully."; } else{ echo "ERROR: Could not able to execute " . $conn->error; } if($conn->query($sql3) === true){ echo "Çontact added successfully."; } else{ echo "ERROR: Could not able to execute " . $conn->error; } // Close connection $conn->close(); ?>

i have this table select MatchID,HomeScore,AwayScore from tbl_matches ; +---------+-----------+-----------+ | MatchID | HomeScore | AwayScore | +---------+-----------+-----------+ | 11 | 1 | 1 | | 12 | 3 | 3 | | 13 | 3 | 0 | | 14 | 3 | 0 | | 15 | 1 | 0 | | 16 | 1 | 0 | | 17 | 0 | 0 | | 18 | 0 | 0 | | 19 | 0 | 1 | | 20 | 0 | 0 | | 21 | 0 | 0 | | 22 | 1 | 1 | | 23 | 0 | 0 | | 24 | 0 | 0 | | 25 | 0 | 0 | | 26 | 0 | 0 | | 27 | 0 | 0 | | 28 | 0 | 0 | | 29 | 1 | 0 | | 30 | 0 | 0 | | 31 | 0 | 0 | +---------+-----------+-----------+ 21 rows in set (0.00 sec) how can i set the unentered result .for now it is like all the games wether played or not are at 0:0.i need to be able to get accurate table log of all the games such that if result is not set this can be identified and not set to 0:0. should i set the default to Null,or not null for the HomeScore and awayscore?

declare l_team_id int; DECLARE cur1 CURSOR FOR SELECT * FROM t_summ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1; SET l_last_row_fetched=0; OPEN cur1; cat_loop: LOOP FETCH cur1 INTO l_team_id,l_teamcat_id; /*work with the data*/ IF l_last_row_fetched=1 THEN /* No more rows*/ LEAVE cat_loop; END IF; if l_team_id = inHometeamId || l_team_id = inAwayteamId THEN insert into mvenue (MatchID ,match_venueID,stadium_LocationID,LeagueSeasonID,CompetitionID,Team_catId)values(inm_id,inVenueId,instadiumId,inseasonId,inCompId,l_teamcat_id); elseif l_team_id = inAwayteamId THEN insert into mvenue (MatchID ,match_venueID,stadium_LocationID,LeagueSeasonID,CompetitionID,Team_catId)values(inm_id,inVenueId,instadiumId,inseasonId,inCompId,l_teamcat_id); else insert into mvenue (MatchID ,match_venueID,stadium_LocationID,LeagueSeasonID,CompetitionID,Team_catId)values(inm_id,inVenueId,instadiumId,inseasonId,inCompId,'0'); end if; END LOOP cat_loop; CLOSE cur1; SET l_last_row_fetched=0; how can i improve on the above to make it database friendli and efficient? how can i modify it to use case statementand leave out the cursor?

iam trying to insert a summary into a table.Is it possible to write a case condition inside an insert statement like so: insert into match_summ(MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID,Team_catId)values('5','6','7','8','9', CASE WHEN homeid = l_team_id THEN teamcat_id=1 END , CASE WHEN l_team_id = awayid THEN teamcat_id=2 ELSE teamcat_id= 0 END ); Is what iam trying to do possible??What is the best way to do it???

how can i get a table like so: from a database table with teams and goals scored Season 2016/17 Premier League 2016/17 Team Information Home Away Total Points Information P W D L F A W D L F A W D L F A +/- Pts Manchester City 7 3 0 0 9 2 3 0 1 9 5 6 0 1 18 7 11 18

how can i leave out all the null rows from this result of query below;iam trying to get the list of player names and their playing position mysql> select f0.player_id as P_id,concat (f0.Fname,' ',f0.Lname)as pname,f1.position_id,f2.position from( select * from Soka_player_details_tbl) as f0 left join (select * from Soka_players_team_tbl where Soka_players_team_tbl.SeasonID=(select SeasonID from tbl_season where Publish='1')and Team_catId= '1' )as f1 on f1.player_id=f0.player_id left join (select * from Soka_position_tbl )as f2 on f1.position_id=f2.position_id order by position_id asc; +------+----------------------------------------------------+-------------+--------------+ | P_id | pname | position_id | position | +------+----------------------------------------------------+-------------+--------------+ | 5 | Performance nutritionist Assistant manager | NULL | NULL | | 8 | Kit manager Equipment manager | NULL | NULL | | 6 | Head of performance Head of medical services | NULL | NULL | | 4 | Masseur Assistant kit manager | NULL | NULL | | 7 | Assistant fitness coach Under-21s assistant coach | 1 | Goal keepers | | 1 | Manager First-team coach | 1 | Goal keepers | | 2 | Goalkeeping coach Fitness coach | 2 | Defenders | | 3 | Club doctor First-team physiotherapist | 3 | Midfielders | | 9 | Football analyst Academy manager | 4 | Forwards | +------+----------------------------------------------------+-------------+--------------+ 9 rows in set (0.00 sec)

how can I get a full table from this result set +-----------+-----------+------+------+--------------+------+--------------+ | HomeScore | AwayScore | g_id | a_id | h_name | h_id | a_name | +-----------+-----------+------+------+--------------+------+--------------+ | 0 | 0 | 11 | 6 | Seowa fc | 7 | Seowa U-14 | | 0 | 0 | 12 | 7 | Seowa U-14 | 6 | Seowa fc | | 0 | 0 | 13 | 8 | Teowa fc | 6 | Seowa fc | +-----------+-----------+------+------+--------------+------+-------------

Hi again, Hopefully the last question as I am not 100% how to solve this one. So on my website someone carried out a search from a search bar using the 'POST' method , teh search results show all whiskies in the databse. I have a number of whiskies with the same name but with different dates and prices. I would like it just to show one of each type that was searched for rather than all of them. I have attahced a clip of the databse. Thanks Index.php </head> <?php $page='index'; include('header.php'); include('navbar.php'); ?> <script type="text/javascript"> function active(){ var search_bar= document.getElementById('search_bar'); if(search_bar.value == 'Search for your whisky here'){ search_bar.value='' search_bar.placeholder= 'Search for your whisky here' } } function inactive(){ var search_bar= document.getElementById('search_bar'); if(search_bar.value == ''){ search_bar.value='Search for your whisky here' search_bar.placeholder= '' } } </script> <body> <div class="third_bar"> <div class="background_image"> </div> <div class="form"><form action= "search.php" method="post"> <input type="text" name="search" id="search_bar" placeholder="" value="Search for your whisky here" max length="30" autocomplete="off" onMouseDown="active();" onBlur="inactive();"/><input type="submit" id="search_button" value="Go!"/> </form> </div> </div> </body> </div> <?php include ('footer.php'); ?> Search.php <?php $page='search'; include('header.php'); include ('navbar.php'); echo "<br>"; include ('connect.php'); if (isset ($_POST['search'])) { //the 'search' refers to the 'search' name=search on the index page and makes does something when the search is pushed. $search = $_POST['search']; $search = "%" . $search . "%"; // MySQL wildcard % either side of search to get partially matching results // No wildcard if you want results to match fully } else { header ('location: index.php'); } $stmt = $conn->prepare("SELECT * FROM test_db WHERE name LIKE :name ORDER BY name ASC"); // Use = instead of LIKE for full matching $stmt->bindParam(':name', $search); $stmt->execute(); $count = $stmt->rowCount(); // Added to count no. of results returned if ($count >= 1) { // Only displays results if $count is 1 or more echo "<div class='results_found'>"; echo $count; echo " results found<br>"; echo "</div>"; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "<div class='results'>"; echo "<div class='result_name'>"; echo "<b>Whisky Name:</b><br>"; echo "<a href='details1.php?id={$row['lot_id']}' >{$row['name']}</a>"; echo"<br>"; echo "</div>"; echo "</div>"; } } else { echo " Sorry no records were found"; } ?> </htm

Hi I have been working on my OOP and have put together some class files to aid my test application ( photo album ) on the upload page I have the browse box, a caption text box and an upload button this page posts to self, Once you click on upload it is also supposed to insert a database entry to allow tracking of the file's attributes, once I click the upload button I get this error message back, " Database Query Failed: Incorrect integer value ' ' for column 'id' at row 1 " so I have re looked over my codes in regards to uploading files and just can not seem to put my mouse on the spot that's causing me an issue so here is the codes that matter to the file uploads... this one is from my database.php class file, public function insert_id() { // get the last id inserted over the current db connection return mysql_insert_id($this->connection); } and this one is one comes from my photograph class file, public function create() { global $database; $attributes = $this->sanitized_attributes(); $sql = "INSERT INTO ".self::$table_name." ("; $sql .= join(", ", array_keys($attributes)); $sql .= ") VALUES ('"; $sql .= join("', '", array_values($attributes)); $sql .= "')"; if($database->query($sql)) { $this->id = $database->insert_id(); return true; } else { return false; } } Looking at my error and the information in those functions I can guess that's where the issue is coming from just don't get why any ideas please?

I want to display 10 products from each category from database, I started to display the categories but how to make displaying the products from each one. Here is the code I make so far. I'm not sure am I doing it right with doble sql select , or it can be done only with one. <div class="inner shadow"> <?php $query = 'SELECT id, title_bg AS `title_cat` FROM categories'; $result = $this->db->query($query); ?> <?php foreach ($result->result() as $row): ?> <?php $title_cat = stripslashes($row->title_cat); ?> <div class="prod-sec"> <div class="prod-head"> <h2><?= $title_cat?></h2> <div class="clear"></div> </div> <?php $query1 = 'SELECT t1.id, t1.title_bg AS `Title`, t1.text_bg AS `Text`, t1.price, t1.discount, t1.category_id, t1.promo_page, t2.id AS FileID, t2.ext, FROM products t1 LEFT JOIN products_pictures t2 ON t1.id = t2.object_id LEFT JOIN categories t3 ON t3.id = t1.category_id WHERE t1.promo_page = 0 AND t1.is_active = 1 AND t3.title_bg = '$title_cat' ORDER BY RAND() LIMIT 10'; $result1 = $this->db->query($query1); ?> <div id="classeslist2"> <ul class="home_middle_products"> <?PHP foreach($result1->result() as $row1) { $f = 'files/products_first_page/' . $row1->id . '.jpg'; if(is_file(dirname(__FILE__) . '/../../' . $f)) { $img = site_url() . "files/products_first_page/".$row1->id.".jpg"; } else { $img = site_url() . "files/products/".$row1->id."/".$row1->FileID."_2.".$row1->ext; } $title = stripslashes($row1->Title); $text = character_limiter(strip_tags(stripslashes($row1->Text)),250); $title_url = getLinkTitle($title); $link = site_url()."products/product/".$row1->id."/{$title_url}"; ?> <li style="width: 185px; height: 270px; margin-left: 3px; margin-top: 10px;"> <div class="thumb"> <a href="<?=$link?>"><img src="<?=$img?>" alt="<?=$title?>" width="182" /></a> <div class="price"><?PHP echo product_price($row1, array('show_discount' => false,"show_old_price"=>false, 'show_label' => false, 'view' => 'no')); ?> </div> </div> <h2></h2> <h2><a href="<?=$link?>"><?=$title?></a></h2> <? $text = substr($text, 0, 100); ?> <? if (strlen($text) == 100) $text .= '...' ; ?> <p><?=$text?></p> </li> <?php } ?> <div class="clear"></div> </ul> </div> </div> <?php endforeach; ?> <div class="clear"></div> </div>

I am wanting to combine the following two query's into a single db hit. $db->query("SELECT id, count(*) total, sum(case when type = 'Leveler OTH' then 1 else 0 end) LevOTH, sum(case when type = 'Mugger OTH' then 1 else 0 end) MugOTH, sum(case when type = 'Buster OTH' then 1 else 0 end) BustOTH FROM othwinners WHERE userid = ?"); $db->execute([$profile->id]); $db->query("SELECT id, count(*) total, sum(case when type = 'Leveler OTD' then 1 else 0 end) LevOTD, sum(case when type = 'Mugger OTD' then 1 else 0 end) MugOTD, sum(case when type = 'Buster OTD' then 1 else 0 end) BustOTD, sum(case when type = 'Mobster OTD' then 1 else 0 end) MobOTD, FROM otdwinners WHERE userid = ?"); $db->execute([$profile->id]); The two on there own work perfect but I am unsure if using INNER JOIN or another JOIN method is going to get me what I am looking for. Both tables have the exact same columns the only difference's are in come cases the $profile->id may or may not exist and if not I will sort those with !num_rows() and the conditions for type change from one table to the other. Columns are id, userid, type, howmany, and timestamp...