Posts

Showing posts with the label sql

Sorting MySQL data into limit sized pages, and onscreen option to change pages? How is it done? [on hold]

Image
Sorting MySQL data into limit sized pages, and onscreen option to change pages? How is it done? [on hold] I am new to HTML and CSS, however I have some basic knowledge of programming games in C# and Java (using frameworks like LibGDX, Unity etc). I've built a database in MySQL and the corresponding php code that displays a html article for each entry in the table. Everything is working great but the whole database loads onto one big webpage. I have been reading around a lot but cannot seem to find where to start on building it into pages. The only idea I can imagine right now is that I will need a field somewhere on my site to hold the current page number the user selected, and then in my SQL SELECT statement use that figure to somehow pull only say 20 pages starting from 'currentPage * 20' for example. I've found small parts of the info I need like I see there may be a LIMIT clause I can use. But how can the SELECT * FROM table ORDER BY id DESC LIMIT (currentPage*20); ...
Post a Comment
Read more

MySQL JOIN whether null or not

MySQL JOIN whether null or not I'm pretty out of practice with MySQL and PHP, but I have a project I'm working on for a friend that involves selecting data from two tables, and combining them into one result - seems simple. Table 1 has 13 fields, but the important ones are id (auto-increment, primary key) and serial (unique). The rest are just ones like customer , description , etc. etc. Pictures has 3 fields, picID (auto-increment, primary key), imagePath and serial I need to retrieve all data from Table 1, and if there is a matching photo (identified by the same serial - only ever 1 photo possible per serial) in Pictures, then retrieve that data too. I then output the data from Table1, and use imagePath from Pictures to build an image in HTML if one has been uploaded. The query I've been using is: $sql = "SELECT * FROM Table1 LEFT JOIN Pictures ON Table1.serial = Pictures.serial ORDER BY Table1.serial"; Which seems perfect, EXCEPT if any row from Table1...
Post a Comment
Read more

SQL Query not updating the database

SQL Query not updating the database I was getting an error from my processing form down below. I'm not sure why it isn't updating to the database when I hit submit. It says it has been successful but yet the databse has not been updated?! I think it maybe because of the "mysqli_stmt_execute" but I'm not sure. Any thoughts :/ Thanks. <?php if(isset($_POST['submit'])){ include_once 'connection.php'; $first = mysqli_real_escape_string($conn, $_POST['first']); $last = mysqli_real_escape_string($conn, $_POST['last']); $uid = mysqli_real_escape_string($conn, $_POST['uid']); $pwd = mysqli_real_escape_string($conn, $_POST['pwd']); $hashedPwd = md5($pwd); //Error Handling //Checking for empty fields if(empty($first) || empty($last) || empty($uid) || empty($pwd)){ header("Location: ../signup.php?signup=empty"); exit(); }else{ //Checking if the input...
Post a Comment
Read more

SQL query to select everything from 1 table and 1 column(same name as in table 1) from another table

SQL query to select everything from 1 table and 1 column(same name as in table 1) from another table I have 3 tables. table1 : Products: ProductId Category Model Price Quantity table2: OrderLines: OrderId ProductId Quantity table3: Orders: OrderId CustomerId (theres also a customer table but it doesnt matter) Now I want to select all the products data from Products table that have ProductId = ProductId in OrderLines table, but get the quantity data (of those same products) from the OrderLines table. Tried all kinds of queries, couldn't find one that works. heres the last thing i kind of tried: SELECT tb1.* ,tb2.[Quantity] FROM [Products] tb1, [OrderLines] tb2 WHERE tb1.[ProductId] IN ( SELECT [ProductId] FROM [OrderLines] WHERE OrderId = @orderId) INNER JOIN [OrderLines] tb2 ON[Products].Quantity = [OrderLines].Quantity"; didn't worked. any suggestions? Thanks a lot. sample data: Orders: OrderId CustomerId 1 2 OrderLines: OrderId ProductId Quantity 1...
Post a Comment
Read more

Select max value from column for every value in other two columns

Select max value from column for every value in other two columns I'm working on a webapp that tracks tvshows, and I need to get all episodes id's that are season finales, which means, the highest episode number from all seasons, for all tvshows. This is a simplified version of my "episodes" table. id tvshow_id season epnum ---|-----------|--------|------- 1 | 1 | 1 | 1 2 | 1 | 1 | 2 3 | 1 | 1 | 3 4 | 1 | 2 | 1 5 | 1 | 2 | 2 6 | 2 | 1 | 1 7 | 2 | 1 | 2 8 | 2 | 1 | 3 9 | 2 | 1 | 4 10 | 2 | 2 | 1 11 | 2 | 2 | 2 The expect output: id ---| 3 | 5 | 9 | 11 | I've managed to get this working for the latest season but I can't make it work for all seasons. I've also tried to take some ideas from this but I can't seem to find a way to add the tvshow_id in there. I...
Post a Comment
Read more

Joining Two Subqueries That Contain A Subquery

Joining Two Subqueries That Contain A Subquery Still new to SQL. I have looked around for an answer on this, and the examples that I have found, I have mirrored my query to look like the examples, and this query still isn't working. All Im trying to do is join two subqueries, but both of these subqueries contain a subquery inside. I'm receiving the following error: Msg 156, Level 15, State 1, Line 26 Incorrect syntax near the keyword 'On'. This is the query, please assist, thank you. Select * From ( Select * From( Select x.ID, x.Date, x.USOHist, x.OVXHist, Abs(Cast((((x.USOHist / NullIf((y.USOHist),0))-1)*100) as Decimal(10,2))) AS '%USOH', Abs(Cast((((x.OVXHist / NullIf((y.OVXHist),0))-1)*100) as Decimal(10,2))) AS '%OVXH' From (Select a.Date as aDate, Max(b.Date) As aPrevDate From USO_OVX_Hist a Inner Join USO_OVX_Hist b on a.Date > b.Date Group By a.Date) Sub1 Inner Join USO_OVX_Hist x on Sub1.aDate = x.Date Inner Join USO_OVX_Hist...
Post a Comment
Read more

How to delete space inside the string under condition, using SQL?

How to delete space inside the string under condition, using SQL? I am looking for your support. I am trying to find and replace part of string in URLs in the mySQL DB. I have over 550 URLs inputted in the code, where I need to delete the spaces. i.e.: https://www.poczytaj.pl/ksiazka/historia-national-geographis-splendor-bizancjum , 317145 ?pp=5 https://www.poczytaj.pl/ksiazka/stara-akademia-platona-w-poczatkach-epoki-hellenistycznej-ostatni-okres-bogdan-dembinski , 413137 ?pp=5 Can you please advice what code I should ran? My proposal which do not works: UPDATE `h15bpt4rj_posts` SET `post_content` = replace(post_content, ', ', ',') WHERE 'post_content' LIKE ', ______?pp=5'; Underscore is exchanging six digits, which are inside the URL. Can you help me to get URLs with SQL like: href="https://www.poczytaj.pl/ksiazka/historia-national-geographis-splendor-bizancjum**,317145**?pp=5" https://www.poczytaj.pl/ksiazka/stara-akademia-platona-w-poczatka...
Post a Comment
Read more

Create Index equivalent from Oracle to SQL Server

Create Index equivalent from Oracle to SQL Server I have a current index that I have pulled from Oracle and I am trying to add to SQL Server. As far as I can tell, I will have to remove the double quotes and use WITH(... However, I cannot find the Maxtrans and other keywords equivalent in SQL Server, especially the M_ROW$$ , which I was thinking it would be the ROW_NUMBER . WITH(... M_ROW$$ ROW_NUMBER CREATE UNIQUE INDEX ""MYDB"".""I_SNAP$MYTABLE"" ON ""MYDB"".""MYTABLE"" (""M_ROW$$"") PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE ""IL_MIS_INDX"" Any help will be greatly appreciated. Thank you! M_ROW$$ is the name of a column o...
Post a Comment
Read more

Fixed length string comparison in Amazon Redshift

Fixed length string comparison in Amazon Redshift I have a table in Amazon Redshift called asmt.questions , which has a field called encodedids . This field is varchar and can have comma separated values. I would like to retreive all the records having any of the following values in them: Amazon Redshift asmt.questions encodedids varchar MAT.GEO.107 MAT.GEO.403 MAT.GEO.409.01 MAT.GEO.504.07 MAT.GEO.901.5 To achieve this, I wrote the following query: SELECT questionid, encodedids, irt_a FROM asmt.questions WHERE ispublic = TRUE AND encodedids similar TO '%(MAT.GEO.107|MAT.GEO.403|MAT.GEO.409.01|MAT.GEO.504.07|MAT.GEO.901.5)%' AND encodedids NOT similar TO '%(MAT.GEO.107.|MAT.GEO.403.|MAT.GEO.409.01.|MAT.GEO.504.07.|MAT.GEO.901.5.)%' AND irt_a IS NOT NULL ORDER BY encodedids, irt_a DESC This query does a decent job, but it also returns records having values like: MAT.GEO.10701 (note the added '01' in the end) MAT.GEO.40301 (note th...
Post a Comment
Read more

Finding Duplicate and Missing (null) Values in Oracle Table

Finding Duplicate and Missing (null) Values in Oracle Table I am looking for errors in a table and want to report both duplicates and missing values. I am unsure of the best way to do this and am looking for advice on a better way to accomplish this. This is in Oracle 12c. This appears to achieve the desired result: SELECT a.id, a.mainfield, a.location, b.counter FROM maintable a INNER JOIN ( SELECT mainfield, Count(*) counter FROM maintable GROUP BY mainfield HAVING Count(mainfield) > 1 OR mainfield IS NULL ) b ON a.mainfield = b.mainfield OR ( a.mainfield IS NULL AND b.mainfield IS NULL ) ORDER BY a.mainfield; This works and gives me the ID, the potentially null MAINFIELD, the location and a count of either the duplicate MAINFIELD values or the null MAINFIELD values. Is there something simpler or potenti...
Post a Comment
Read more

Query to Output SQL table to Excel or CSV

Query to Output SQL table to Excel or CSV I have been trying to export my table to a csv through a SQL query and no luck. The attempt was using the OUTPUT function but I got an error. Below is the query: SELECT * FROM [table]; OUTPUT TO 'C:foldernew_data.csv' FORMAT TEXT QUOTE '"' WITH COLUMN NAMES; This was the error: Msg 102, Level 15, State 1, Line 81 Incorrect syntax near 'OUTPUT'. Msg 319, Level 15, State 1, Line 84 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Am I not using the OUTPUT function correctly and is there an alternative query in SQL? the ";" after the SELECT terminates the statement. So you have two statements here: SELECT ...; and OUTPUT TO ...; Remove the semicolon after the select and see if that works. docs.microsoft...
Post a Comment
Read more

SQL ont-to-may relationship - How to SELECT rows depending on multiple to-many properties?

SQL ont-to-may relationship - How to SELECT rows depending on multiple to-many properties? A MySQL database contains two tables with a one-to-many relationship: One user can have many settings: Users: id username password -------------------------- 1 Bob 123 2 Alice abc ... Settings: id user_id key value ----------------------------- 1 1 color blue // Bobs settings... 2 1 theme xy 3 1 size 5 4 2 size 6 // Alices settings... Problem: How to find all users with color == blue AND size == 5 ? color == blue AND size == 5 Using a LEFT JOIN it is no problem to find users with one property: LEFT JOIN SELECT users.id FROM users LEFT JOIN settings ON users.id = settings.user_id WHERE settings.key = 'color' AND settings.value = 'blue' However, this does not work when searching for two settings at a time? Is it possible to solve this with a single statement? What is the most efficient...
Post a Comment
Read more

output name instead of id

output name instead of id I'm trying to see how long it takes to resolve a bug based on the area that it affects. My result set looks like this areaid Average Resolution/Closure Time (days) 1 425.57929613 2 510.00435347 3 342.90670739 I want to replace the area values with the name of the area rather than the id number. The name of the area is in another table (area table). the table i'm performing the code on is called bug table. So this is what I want. area_name Average Resolution/Closure Time (days) ABC 425.57929613 DEF 510.00435347 GHI 342.90670739 The area table looks like this areaid area_name 1 ABC 2 DEF 3 GHI i think i need to use a join (all bugs is tied to one area, but area categories may not be tied to a bug) so that my code is able to call the area name based on the primary key area id. My question is basically how...
Post a Comment
Read more

Select all substrings that are contained in a string

Select all substrings that are contained in a string I have a string that contains multiple substrings that are separated by a delimiter character. substr1#substr2#substr3...#substrN substr1#substr2#substr3...#substrN I want to query for all the values in a column that are also in this string. What I have so far is: SELECT substring_col FROM table WHERE id IN SUBSTR(str_to_search,INSTR(str_to_search,substring_col),INSTR(str_to_search,'#',INSTR(str_to_search,substring_col))-1) However, this only returns the first substring that is found. How can I make it return all substrings that are found? Could you share an example? This sounds easy but somehow I want to confirm if I am getting what you are asking. – trollster Jun 29 at 14:33 Probably duplicate of stackoverflow.com/questions/51045592/extract-nth-subst...
Post a Comment
Read more