Search the Community

<?php // SET variables $servername = ******* your server $username = ******* your user name $password = ******* your password $database = ******* your database $table = ******* table name to return column names to assign to $_SESSION[$column] $column = ******* column to query $value = ******* column value to match column query /** WARNING avoid using on databases where column contents are large eg. BLOBs **/ // Create connection to database $conn = new mysqli($server, $username, $password, $database); if (!$conn) { die("Connection failed: " . mysqli_connect_error()); // echo 'con failed'; } if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Database configuration $db = new mysqli($server, $username, $password, $database); // Check connection if ($db->connect_error) { die("Connection failed: " . $db->connect_error); } // Query to get columns from table $query = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = "'.$database.'" AND TABLE_NAME = "'.$table.'"'; //echo $query; $resultcols = $conn->query($query); //print_r($_SESSION); while($rowcols = $resultcols->fetch_assoc()) { $sql = ' SELECT `'. $rowcols["COLUMN_NAME"].'` FROM `'.$table.'` where `'. $column .'` = "'.$value.'"'; $result = $conn->query($sql); // print_r($_SESSION); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { $_SESSION[$rowcols["COLUMN_NAME"]] = $row[$rowcols["COLUMN_NAME"]]; } $err_message = "Success<br>"; } else { $err_message = "unknown information can not complete session variables<br>"; } } // see what you get print_r($_SESSION); echo "<br>".$err_message."<br>"; foreach($_SESSION as $x => $x_value) { echo "SESSION Key=" . $x . ", SESSION Value=" . $x_value; echo "<br>"; } ?>

Hi All I have the following code in SQL to read in the data from a JSON file and INSERT it into 2 tables. I would like to make this a stored procedure with a parameter of the file location, however when I do that it it gives me an error saying its expecting a string where I replace the file location with @file Is there a solution to this? This is the code: -- Declare variable DECLARE @json nvarchar(max); -- Upload JSON data into that variable SELECT @json = BulkColumn FROM OPENROWSET ( BULK 'C:\TEMP\Scaler\Customers.json', SINGLE_CLOB ) AS [Json]; -- Select the Job Details from that variable INSERT INTO OC_JobDetails ( SourceFile, WFD ) SELECT * FROM OPENJSON(@json, '$.JobDetails') WITH ( [SourceFile] varchar(255) '$.SourceFile', [WFD] varchar(255) '$.WFD' ); --Get JobID of JobDetails entry DECLARE @jobID Int SET @jobID = ( SELECT MAX(JobID) FROM OC_JobDetails ) -- Select the Record Details from that variable INSERT INTO OC_RecordDetails ( ID, Name, JobID ) SELECT *, @jobID FROM OPENJSON(@json, '$.JobDetails.Customers') WITH ( CustomerID varchar(255) '$.CustomerID', [Name] varchar(255) '$.Name' ); Then all I am doing is adding the create procedure at the top CREATE PROCEDURE spImportJSON @File nvarchar(255) AS And replacing 'C:\TEMP\Scaler\Customers.json' iwth @File JSON file attached if anyone wanted to try replicating. Thanks David

I don't have MySQL experience, I need some help to write a query for my work. I need a query that count transaction for a week, for example, I wanna run the query every Friday and want to see the result from last Friday to Thursday, I mean just last week's data. The start date is last Friday (11/12/2021) and the End date is Thursday (11/18/2021). It is a weekly process that I can run every week to download the output. There are two tables, sales, and customers. The customer table has fields such as "customer_id", FirstName, LastName, etc and the sales table has "id" which is the same as customer_id from customer table, salesDate, etc. I want to count data of those who are customer_id match to the sales table. I would like to ignore some of the users as well like our IT team has a couple of test users that uses for testing. for example testuser1 and testuser2. I would like the query to not count these two users. Here is an example that I want to make work. Can someone please help me to write the query? SELECT COUNT(CustomerID) AS NumberOfCustomer FROM Customer INNER JOIN Sales ON ID = CustomerID where user != (testuser1 and testuser2 where sales date between last Friday to Thursday; Thanks!

I'm working on this session, and it shows if I'd like to test NULL value, I'll need to use below syntax: However, I'd like to know what if I have no idea which column(s) has(have) NULL value? How should I make a query to look for ANY record that includes NULL value without writing specific column_name?

Hi There, I have two questions about SQL syntax, the first one is about "SELECT DISTINCT" and the other is "NOT". Question 1: For example, "Customers" table has fields including CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country. From my understanding, if my SQL statement is SELECT DISTINCT Country FROM Customers; then it will show the list of unique countries. However, I'd like to know what does it mean if my SQL statement is SELECT DISTINCT Country, City, CustomerName FROM Customers; ? Why I don't have to write SELECT DISTINCT Country, DISTINCT City, DISTINCT CustomerName? You may refer to this session: https://www.w3schools.com/sql/sql_distinct.asp Question 2: I'm working on the session, and there's an example as below: I understand that this example would like to exclude BOTH Country = "Germany" and Country = "USA", but I don't understand why using the operator [AND] instead of [OR]? Doesn't [AND] represent the intersection while [OR] mean union? So, if I want to exclude BOTH "Germany" and "USA", why I can't use [OR] as the operator in this example?

I have two tables with primary key and foreign key (ItemId) Table 1 (Items) ItemId ItemName OPStock OPStockValue 1 Pen 100 1000 2 Pencil 50 5000 Table 2 (Inventory) TransactionType VchNo ItemID ItemName Qty TaxableAmt EntryDate Sale 2 1 KFL UREA NEEM COTED 70 16940 16-04-2020 Sale 1 5 IPL MOP 60 49200 16-04-2020 Sale 3 2 IPL DAP (IMPORTED) 60 58800 16-04-2020 Sale 4 2 IPL DAP (IMPORTED) 10 9800 16-04-2020 Sale 4 5 IPL MOP 50 41000 16-04-2020 Sale 5 2 IPL DAP (IMPORTED) 10 9800 23-04-2020 Purchase 1 67 PADDY SEEDS DAYAL GOLD 30 201000 23-04-2020 Purchase 1 97 PADDY SEEDS DAYAL GOLD 15 100500 23-04-2020 Sale 8 2 IPL DAP (IMPORTED) 10 9800 24-04-2020 Purchase 2 1 KFL UREA NEEM COTED 1600 381008.16 24-04-2020 Purchase 3 7 CASTER OIL CAKE ORGANIC 500 261250 24-04-2020 Purchase 4 38 PADDY SEEDS MANSURI 175 682500 02-05-2020 Sale 19 39 PADDY SEEDS MTU-1001 5 20750 02-05-2020 Sale 19 40 PADDY SEEDS MTU7029 1.25 4812.5 03-05-2020 Now When I Pass @FromDate and @Todate I want to output like below: EntryDate ItemName OpQty PurchQty SaleQty ClosingBal database link What I have tried: I am trying to solve using Left Join But the result is not correct SELECT I.ItemID, I.ItemName, I.OPStock AS OpStock, I.OPStockValue AS OpValue, SUM(SP.Qty) AS InQty, SUM(SP.TaxableAmt) AS InValue, SUM(SS.Qty) AS OutQty, SUM(SS.TaxableAmt) AS OutValue FROM items AS I LEFT JOIN inventory AS SP ON SP.ItemID = I.ItemID LEFT JOIN inventory AS SS ON I.ItemID = SS.ItemID WHERE SP.TransactionType = 'Purchase' AND SS.TransactionType = 'Sale' GROUP BY I.ItemID ORDER BY I.ItemName ASC

Hello, my question is regarding the code below. I usually see double left joins which refer to the table1 for both LEFT JOINS (FROM cities/cities as tabl1), this one is referring to countries.code as table1 in the second LEFT JOIN which confuses me somewhat. Is the first or second LEFT JOIN excecuted first? Basically I'm asking which has priority and which table gets LEFT JOINed first. SELECT cities.name AS city, urbanarea_pop, countries.name AS country, indep_year, languages.name AS language, percent FROM cities LEFT JOIN countries ON cities.country_code = countries.code LEFT JOIN languages ON countries.code = languages.code ORDER BY city, language; SELECT cities.name AS city, urbanarea_pop, countries.name AS country, indep_year, languages.name AS language, percent from languages right join countries on languages.code = countries.code right join cities on countries.code = cities.country_code order by city, language;

I would like to connect to a mysql database that is a localhost connection and not on a internet service provider. How do i connect? Do you use "localhost or your ip"? I tried it and it does not work. Also, how do i open a port for access? Is the port connections blocking me from succeeding regarding this?

I want to know how I would go about making a script where a user would type in a message and post that said message. Is sql required?

Each SQL function and capability should indicate if it is ANSI standard compliant and the version in which it was first found and the earliest version of the database which supported it. SQL-86 SQL-89 SQL-92 SQL:1999 SQL:2003 SQL:2006 SQL:2008 SQL:2011 SQL:2016 SQL:2019

I am building a website and want to test it for SQL injection vulnerability, when I run the injection, where would I view the results?

Hi all, Newbie to the forum and the world of SQL, hence apologies for my naivety. From the attached table, I'm trying to create a range (as an output) and create a summary as shown in the attached in yellow. The reason being, the size of raw data (+2m rows). Trying to do this ins Microsoft SQL Management Studio. Any help would be greatly appreciated. Thank you. SQL.xlsx

I am stuck with a problem. Have a shopping cart, once the client selects the products, from a SQL table (1), they are taken up by an array variable A session array variable delivers values using foreach loop. The php shows all the values sequentially But the SQL table (2) gets updated with only the last value. Understand that this will happen due to looping. So how to go about resolving this

Just to preface: Thank you W3Schools for these tutorials!!! Now on to the possible issue: In your SQL Tutorial I am unable to view the "SQL Intro" page. When I click on it I keep getting "502 - Bad Gateway." I've tried navigating to it via the "Next" button at the bottom of the "SQL HOME" page but I get the same message. The URL it is trying to access is "https://www.w3schools.com/sql/sql_intro.asp" I was unsure if this was something others were seeing or just something on my end.

Hi, I am not sure how to write a query for the below case, Pls help me out. ID description values M1 ab1 23 M1 ab2 54 M1 ab3 23 M2 ab1 67 M2 ab2 56 M2 ab3 91 M3 ab1 41 M3 ab2 53 M3 ab3 27 M3 ab4 41 Conditions: I need to pick the row when values are same for different description under similar ID, Example: Under ID (M1) , I have the description (ab1 and ab3) have same values (23), Similarly ID (M3) have same values 41 for descripttion ab1 and ab4. hence the result must be only Red texted in the table. Thanks In advance.

I am joining 2 tables using the query below. Its many to many relationship. The join works fine but creates multiple rows for each item which is expected but the Amount/value column is also duplicated. How to avoid this? Thanks As you can see the Current output the value (50000 appears 3 times instead of 1 and 27000 appears 3 times instead of 1) SELECT * FROM T1 INNER JOIN T2 ON T1.Projectex = T2.WBS_Parent Table 1: +-----------+------------+-----------+----------+--------+----------+-------+ | Projectex | CAPEX_OPEX | costelmnt | sap_vers | Period | FISCYEAR | VALUE | +-----------+------------+-----------+----------+--------+----------+-------+ | 0-01081 | CAPEX | 3416 | 61 | 3 | 2020 | 50000 | | 0-01081 | OPEX | 7077 | 30 | 5 | 2020 | 27000 | +-----------+------------+-----------+----------+--------+----------+-------+ Table2: +------------+-----------------+---------+---------+ | WBS_PARENT | FINANCILAL_YEAR | MEASURE | AMOUNT | +------------+-----------------+---------+---------+ | 0-01081 | 2020 | CPX | 2000000 | | 0-01081 | 2020 | OPX | 50000 | | 0-01081 | 2020 | OPX | 1000000 | +------------+-----------------+---------+---------+ CURRENT OUTPUT: Projectex| CAPEX_OPEX| costelmnt| sap_vers| Period| FISCYEAR| VALUE| 0 0-01081| CAPEX| 3416| 61| 3| 2020| 50000| 1 0-01081| CAPEX| 3416| 61| 3| 2020| 50000| 2 0-01081| CAPEX| 3416| 61| 3| 2020| 50000| 3 0-01081| OPEX| 7077| 30| 5| 2020| 27000| 4 0-01081| OPEX| 7077| 30| 5| 2020| 27000| 5 0-01081| OPEX| 7077| 30| 5| 2020| 27000| WBS_PARENT FINANCILAL_YEAR MEASURE AMOUNT 0 0-01081| 2020| CPX| 2000000 1 0-01081| 2020| OPX| 50000 2 0-01081| 2020| OPX| 1000000 3 0-01081| 2020| CPX| 2000000 4 0-01081| 2020| OPX| 50000 5 0-01081| 2020| OPX| 1000000 Expected Output: Projectex| CAPEX_OPEX| costelmnt| sap_vers| Period| FISCYEAR| VALUE| 0 0-01081| CAPEX| 3416| 61| 3| 2020| 50000| 1 0-01081| CAPEX| 3416| 61| 3| 2020| 2 0-01081| CAPEX| 3416| 61| 3| 2020| 3 0-01081| OPEX| 7077| 30| 5| 2020| 27000| 4 0-01081| OPEX| 7077| 30| 5| 2020| 5 0-01081| OPEX| 7077| 30| 5| 2020| WBS_PARENT FINANCILAL_YEAR MEASURE AMOUNT 0 0-01081| 2020| CPX| 2000000 1 0-01081| 2020| OPX| 50000 2 0-01081| 2020| OPX| 1000000 3 0-01081| 2020| CPX| 4 0-01081| 2020| OPX| 5 0-01081| 2020| OPX|

I have a problem with an XML file that has the following structure: <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <DATAPACKET Version="2.0"> <METADATA><FIELDS> <FIELD attrname="Id" fieldtype="i4" readonly="true" SUBTYPE="Autoinc"/> <FIELD attrname="Blocco" fieldtype="string" WIDTH="5"/> <FIELD attrname="Domanda" fieldtype="string" WIDTH="2"/> <FIELD attrname="Risposta" fieldtype="boolean"/> <FIELD attrname="Capitolo" fieldtype="string" WIDTH="2"/> <FIELD attrname="Indice" fieldtype="string" WIDTH="3"/> <FIELD attrname="Argomento" fieldtype="string" WIDTH="1"/> <FIELD attrname="SubArgomento" fieldtype="string" WIDTH="2"/> <FIELD attrname="Figura" fieldtype="string" WIDTH="4"/> <FIELD attrname="FiguraBlk" fieldtype="string" WIDTH="4"/> <FIELD attrname="Difficolta" fieldtype="string" WIDTH="2"/> <FIELD attrname="Testo" fieldtype="string" WIDTH="320"/> <FIELD attrname="Lingua1" fieldtype="string" WIDTH="320"/> <FIELD attrname="Lingua2" fieldtype="string" WIDTH="320"/> <FIELD attrname="Lingua3" fieldtype="string" WIDTH="320"/> <FIELD attrname="Commento" fieldtype="string" WIDTH="256"/> <FIELD attrname="Aiuto" fieldtype="string" WIDTH="128"/> <FIELD attrname="Foto1" fieldtype="string" WIDTH="5"/> <FIELD attrname="Foto2" fieldtype="string" WIDTH="5"/> <FIELD attrname="Foto3" fieldtype="string" WIDTH="5"/> <FIELD attrname="Foto4" fieldtype="string" WIDTH="5"/> <FIELD attrname="Foto5" fieldtype="string" WIDTH="5"/> <FIELD attrname="Video1" fieldtype="string" WIDTH="5"/> <FIELD attrname="Edl1" fieldtype="string" WIDTH="15"/> <FIELD attrname="Video2" fieldtype="string" WIDTH="5"/> <FIELD attrname="Edl2" fieldtype="string" WIDTH="15"/> <FIELD attrname="Video3" fieldtype="string" WIDTH="5"/> <FIELD attrname="Edl3" fieldtype="string" WIDTH="15"/> <FIELD attrname="Audio1" fieldtype="string" WIDTH="12"/> <FIELD attrname="Audio2" fieldtype="string" WIDTH="12"/> <FIELD attrname="Audio3" fieldtype="string" WIDTH="12"/> <FIELD attrname="Html1" fieldtype="string" WIDTH="5"/> <FIELD attrname="Html2" fieldtype="string" WIDTH="5"/> <FIELD attrname="Html3" fieldtype="string" WIDTH="5"/><FIELD attrname="Libro1" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro1PosY" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro2" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro2PosY" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro3" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro3PosY" fieldtype="string" WIDTH="5"/> <FIELD attrname="Info1" fieldtype="string" WIDTH="120"/> <FIELD attrname="Info2" fieldtype="string" WIDTH="120"/> <FIELD attrname="Gruppo1" fieldtype="string" WIDTH="3"/> <FIELD attrname="Gruppo2" fieldtype="string" WIDTH="3"/> <FIELD attrname="Gruppo3" fieldtype="string" WIDTH="3"/> </FIELDS><PARAMS AUTOINCVALUE="7166"/></METADATA> <ROWDATA> <ROW Id="2" Blocco="11023" Domanda="02" Risposta="TRUE" Capitolo="01" Indice="A01" Argomento="A" SubArgomento="1" Figura="" FiguraBlk="" Difficolta="6" Testo="I ciclomotori possono avere due o tre ruote" Lingua1="Les motocycles légers peuvent avoir deux ou trois roues" Lingua2="Kleinkrafträder können zwei oder drei Räder haben" Lingua3="I ciclomotori possono avere due o tre ruote" Commento="infatti i CICLOMOTORI possono avere DUE, TRE e anche QUATTRO RUOTE, cilindrata fino a 50 cm³ e velocità fino a 45 km/h." Aiuto="Classificazione dei veicoli." Foto1="3113" Foto2="" Foto3="" Foto4="" Foto5="" Video1="" Video2="" Video3="" Audio1="04023_40231" Audio2="" Audio3="" Html1="" Html2="" Html3="" Libro1="1" Libro2="1" Libro3="" Info1="11023" Info2="Ciclomotori"/> <ROW Id="3" Blocco="11023" Domanda="03" Risposta="TRUE" Capitolo="01" Indice="A01" Argomento="A" SubArgomento="1" Figura="" FiguraBlk="" Difficolta="5" Testo="Non tutti i veicoli a motore a due ruote vengono classificati ciclomotori" Lingua1="Pas tous les véhicules à moteur à deux roues peuvent être classifiés des motocycles légers" Lingua2="Nicht alle zweirädrigen Kraftfahrzeuge werden als Kleinkrafträder eingestuft" Lingua3="Non tutti i veicoli a motore a due ruote vengono classificati ciclomotori" Commento="infatti vengono CLASSIFICATI CICLOMOTORI solo i veicoli a DUE RUOTE con CILINDRATA NON SUPERIORE a 50 cm³ e VELOCITÀ NON SUPERIORE a 45 km/h." Aiuto="Classificazione dei veicoli." Foto1="1238" Foto2="" Foto3="" Foto4="" Foto5="" Video1="" Video2="" Video3="" Audio1="04023_40232" Audio2="" Audio3="" Html1="" Html2="" Html3="" Libro1="1" Libro2="1" Libro3="" Info1="11023" Info2="Ciclomotori"/> it continues with this structure but it is very long. How can I do from my SQL DB to extract a data ("Libro3") to insert it inside every occurrence of ''Libro3' of XML file? In my sql to recognize the line to be modified I have Id,Blocco, Libro3 obviously, but i don t know how i can modify the file. to recognize the line to be modified on the sql I have line, id and block

ID Name Orderdate Catalog Price 7b 34-10 NULL 3000 7b 34-10 NULL 3000 7b 34-10 NULL 2000 7b 35-12 PL-17 3000 8b 35-11 PL-18 4000 8b 34-13 PL-18 4000 8b 34-14 PL-18 4000 8b 34-15 PL-18 4000 9b 35-12 PL-19 5000 9b 35-11 PL-19 5000 9b 34-18 PL-19 5000 9b 34-19 PL-19 5000 9b 34-20 PL-19 5000 I want a List of the products where Id starts with 7 where Name starts with 34 where Orderdate = null whit the highest catalog price Output should be this ID Name Orderdate Catalog Price 7b 34-10 NULL 3000 7b 34-10 NULL 3000

Hello, I have installed different versions of SQL Server in my laptop (Asus X44H), but here is the error -> (provider: Named pipes provider, error:40- could not open a connection to SQL Server)(Microsoft SQL Server, Error:2) SQL Server instance MSSQLSERVER could not be installed, I don't know why! (I checked that in services.msc, there is no MSSQLSERVER), I have also changed my windows-10 but the issue has not been resolved yet. Any Solutions? Thanks.

Hi . I'm having trouble with the AUTO INCREMENT statement in SQL. I've built the table : CREATE TABLE tenants( apartment_number_first_floor INT AUTO_INCREMENT, family_name VARCHAR (12) DEFAULT NULL, sur_name VARCHAR (8) DEFAULT NULL, telephone_number INT(3) NOT NULL, PRIMARY KEY (apartment_number_first_floor) ); when inserting a row: INSERT INTO tenants VALUES(' ',' '); The IDE doesn't accept the column count: ER_WRONG_VALUE_COUNT_ON_ROW: Column count doesn't match value count at row 1 So i tried filling the rows just to check if the IDE will run the statement INSERT INTO tenants VALUES(103,'שקלובין','מרתה',203); but the IDE refuses to increment Any ideas? Thanks

Hi, How can I show string results as characters in sql plus? select* from example a where a.insert_time>='12-dec19' and a.status=1 as 'Cars I want that if 'status' field status result would show '1' the the output would show 'Cars' and if the result would show '2' the the output would show 'Bikes' instead of strings. Best regards

Hi, How can I show string results as characters in sql plus? select* from example a where a.insert_time>='12-dec19' and a.status=1 as 'Cars I want that if 'status' field status result would show '1' the the output would show 'Cars' and if the result would show '2' the the output would show 'Bikes' instead of strings. Best regards

Hi I have a query that returns the count of entries based on CURDATE(), it works for any dat in this month (April) but does not work for next month (May). I think it is something to do with CURDATE()+3 returns 2018-04-31 which should be 2018-05-01 This Works as Date is in April SELECT COUNT(Machine) FROM production WHERE Status = 'Confirmed' AND DATE(FROM_UNIXTIME(production.Required)) = CURDATE() This does not work as in May SELECT COUNT(Machine) FROM production WHERE Status = 'Confirmed' AND DATE(FROM_UNIXTIME(production.Required)) = CURDATE()+3

There is a table, named student_mark and I have to find which 2 students are having min marks.I also tried group by but it is not working.