Search the Community
Showing results for tags 'sql'.
-
MySQL after update trigger with number of affected rows condition
thara posted a topic in MySQL Help
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?
- 10 replies
-
- table
- database php
-
(and 3 more)
Tagged with:
-
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(); ?>
- 12 replies
-
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(); ?>
-
Hello Everyone - I am playing around with some MYSQL and PHP project I have. I ran into a complex problem getting a PHP table filled with data from MYSQL. I will try to explain what I am trying to do: I am trying to do something like this but in PHP. This is my data from MYSQL database. The Table is called Children This is a quick explanation of how each column on the first screenshot should be filled from the database. This code is what I have so far... to be honest i am not sure how to get the totals of rest of the columns. Maybe use I can use subqueries or if statements... not sure! Can you please help me out? $r = mysqli_query($dbc,"SELECT Classrooms.ClassroomName, COUNT(*) AS TotalChildren FROM Children JOIN Classrooms ON Children.classroomID = Classrooms.classroomID GROUP BY Classrooms.ClassroomName");
-
I am creating a table that creates many rows based on what is in the equipment table. There is a second table that has information pertaining to an order that has already been made. equipment table is as follows: equipment_id equipment_name 1 Book 2 Dog 3 cat 4 hat equipment order table is as follows: job_id equipment_id equipment_quantity 10 1 100 10 3 100 There are 20 things in the equipment table. When the page loads, i would like it to show all of the items from the equipment table but i would like the rows where there is a quantity defined in the equipment_order table to be populated. So i should see Eauipment Quantity Book 100 Dog cat 100 hat The sql that i have used is this SELECT a.equipment_id, equipment_name, equipment_category, b.equipment_quantity FROM ssm_equipment a LEFT JOIN ssm_equipment_order b ON a.equipment_id = b.equipment_id Quite rightly this select anything with a quantity regardless of whether it relates to the current job or not. I have a variable $current_job_id that needs to be used to only get the values for the current job I tried the following SELECT a.equipment_id, equipment_name, equipment_category, b.equipment_quantity FROM ssm_equipment a LEFT JOIN ssm_equipment_order b ON a.equipment_id = b.equipment_id WHERE $current_job_id = 10 but this only get the rows where the ids match. I tried moving the WHERE clause but i cant seem to figure this out with out getting many errors. I have also tried the followin to no avail. SELECT a.equipment_id, equipment_name, equipment_category, b.equipment_quantity, b.job_id FROM ssm_equipment a LEFT JOIN ssm_equipment_order b ON a.equipment_id = b.equipment_id where b.job_id = '24' or b.job_id='NULL' The above just shows row with 24 I have talked about this in another thread but as this is very different to the origional question, i have moved it. Origional questions is below
-
i was working on a mini project that imports csv file to the database through ajax and it's working fine <?php if(!empty($_FILES["marks_file"]["name"])) { $connect = mysqli_connect("localhost", "root", "", "dbname"); $output = ''; $allowed_ext = array("csv"); $extension = end(explode(".", $_FILES["marks_file"]["name"])); if(in_array($extension, $allowed_ext)) { $file_data = fopen($_FILES["marks_file"]["tmp_name"], 'r'); fgetcsv($file_data); while($row = fgetcsv($file_data)) { $name = mysqli_real_escape_string($connect, $row[0]); $Physics = mysqli_real_escape_string($connect, $row[1]); $Maths = mysqli_real_escape_string($connect, $row[2]); $Chemistry = mysqli_real_escape_string($connect, $row[3]); $Biology = mysqli_real_escape_string($connect, $row[4]); $SST = mysqli_real_escape_string($connect, $row[5]); $query = " INSERT INTO csv (name, Physics, Maths, Chemistry, Biology, SST) VALUES ('$name', '$Physics', '$Maths', '$Chemistry', '$Biology' , '$SST') "; mysqli_query($connect, $query); } $select = "SELECT * FROM csv ORDER BY id DESC"; $result = mysqli_query($connect, $select); $output .= ' <table class="table table-bordered"> <tr> <th width="25%" >name</th> <th width="15%" >Physics</th> <th width="15%" >Maths</th> <th width="15%" >Chemistry</th> <th width="15%" >Biology</th> <th width="15%" >SST</th> </tr> '; while($row = mysqli_fetch_array($result)) { $output .= ' <tr> <td>'.$row["name"].'</td> <td>'.$row["Physics"].'</td> <td>'.$row["Maths"].'</td> <td>'.$row["Chemistry"].'</td> <td>'.$row["Biology"].'</td> <td>'.$row["SST"].'</td> </tr> '; } $output .= '</table>'; echo $output; } else { echo 'errorx'; } } else { echo "errory"; } ?> however the imported csv files inserts null values in the tables because the format of all csv files assigned to me are in the exact same format: ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,Fields,Physics~75,Maths~50,Chemistry~65,Bio~85,SST~100 ,,,Name1,10,25,35,42,62 ,,,Name2,80,45,45,45,25 ,,,Name3,63,25,63,36,36 ,,,Name4,82,36,75,48,42 ,,,Name5,45,45,78,25,24 ,,,Name6,36,36,15,75,36 ,,,Name7,99,45,24,24,45 ,,,Name8,45,85,85,85,96 i changed my code a bit modified the functions to espace blank spaces and not return null values in the data tables while (($row = fgetcsv($file_data, 1000, ",")) !== FALSE) { if ((string) $row[0] != '0' and empty($row[0])) { continue; } $name = mysqli_real_escape_string($connect, $row[0]); $Physics = mysqli_real_escape_string($connect, $row[1]); $Maths = mysqli_real_escape_string($connect, $row[2]); $Chemistry = mysqli_real_escape_string($connect, $row[3]); $Biology = mysqli_real_escape_string($connect, $row[4]); $SST = mysqli_real_escape_string($connect, $row[5]); $query = " INSERT INTO csv (name, Physics, Maths, Chemistry, Biology, SST) VALUES ('$name', '$Physics', '$Maths', '$Chemistry', '$Biology' , '$SST') "; mysqli_query($connect, $query); } But it doesn't work with the specified csv files i think the ajax call breaks and that's why nothing happens.However it works fine with csv files without blank spaces
-
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