ChenXiu Posted July 1, 2023 Share Posted July 1, 2023 I have tried several "UNION ALL" queries, and I either get: • "undefined index" errors or • when selecting from tables with same column names, I can't differentiate which table the column belongs to • I get alot of empty results Here are some examples I have tried: $query = "SELECT description as ffff, NULL as gggg FROM myTable WHERE sku = '1234' UNION ALL SELECT description as ffff, weight as gggg FROM my_other_Table WHERE sku = '1234'" $result = $db->query($query); while ($row = $result->fetch_assoc()) { echo $row['fff']; echo $ow['gggg']; } $query = "SELECT description as ffff, NULL as gggg FROM myTable WHERE sku = '1234' UNION ALL SELECT color as uuuuuu, weight as pppppppppp FROM my_2nd_Table WHERE sku = '1234'" UNION ALL SELECT color as wwxxyyzz, weight as mmmnnnoooppp FROM my_3rd_Table WHERE sku = '1234'" $result = $db->query($Query); while ($row = $result->fetch_assoc()) { echo $row["ffff"]; echo $row["uuuuuu"]; echo $row["pppppppppp"] echo $row["wwxxyyzz"]; echo $row["mmmnnnoooppp"]; } I think I am almost on the right 'track' but the train conductor wants his ticket :-) Quote Link to comment Share on other sites More sharing options...
Barand Posted July 1, 2023 Share Posted July 1, 2023 24 minutes ago, ChenXiu said: "undefined index" errors The first query defines the column names for all results in a union, so in your second example you will have columns ffff, gggg only. 24 minutes ago, ChenXiu said: when selecting from tables with same column names, I can't differentiate which table the column belongs to Use a dummy identifying column SELECT description as ffff , NULL as gggg , 'A' as source FROM myTable WHERE sku = '1234' UNION ALL SELECT description , weight , 'B' FROM my_other_Table WHERE sku = '1234' [edit] Why have you got multiple tables with the same columns in the first place? 24 minutes ago, ChenXiu said: I get alot of empty results ??? Quote Link to comment Share on other sites More sharing options...
ChenXiu Posted July 1, 2023 Author Share Posted July 1, 2023 Barand, thank you. Okay, I think I understand. It sounds like if the first query defines the column names, then maybe what I should do is make my first query the longest query with the most actual columns, and set the alias names there to be used for the remaining "union select" subqueries. Regarding using a "dummy column" to differentiate, I am not sure how PHP echoes a dummy column, would it be something like this: $result = $db->query($query); while($row=$result1->fetch_assoc()) { echo $row["A"."description"]; }( ...probably not, but I want to demonstrate that I am trying 😀 ) The reason some of the columns have the same names is because I have never used "union select" before, so never needed to differentiate, and there is often lots of duplication amongst the data I receive from various sources (e.g. all the sources seem to provide "color" and "description," of which only one of them may provide "weight" or "dimensions," etc. So when I built the mySQL tables, I didn't know in advance I would ever need to differentiate between which table I needed, for example, the color from. I suppose the easiest thing would be to simply rename the table columns so they are all different (takes 5 minutes max to do this), but then I won't learn the new stuff :-) Regarding empty results -- Yes! For whatever reason, out of the 100's of experiments I did earlier today, while looping through the "while ($row = $result->fetch_assoc())" loops, PHP would always echo empty results in seemingly random spots. My "fix" on this was this: $dog = !empty($row["columnName"]) ? $row["columnName"] : ""; $horse = !empty($row["other_columnName"]) ? $row["other_columnName"] : ""; $bird = !empty($row["columnSomething"]) ? $row["columnSomething"] : ""; echo $dog . $bird . $horse, etc., etc. I really want to learn how to make the, "Union Select" technique work! I did some benchmark loops of 1000 queries (union 3 queries, versus 3 queries in sequence, ran 1000 times each) and found that "Union Select" is twice as fast! Instead of taking 30 nanoseconds for a 1000-query loop, it only takes 15 nanoseconds. That being said, just running a "union query" once will only save me 15 picoseconds. However, when you get to be my age, every picosecond counts. Quote Link to comment Share on other sites More sharing options...
kicken Posted July 1, 2023 Share Posted July 1, 2023 39 minutes ago, ChenXiu said: I am not sure how PHP echoes a dummy column, would it be something like this: From the PHP perspective your dummy column is the same as any other column. If you want to echo it, you just echo $row['dummyColumnName']. You probably don't want to echo it though, you want to use it to determine which table the row came from using a simple if. if ($row['dummyColumnName'] === 'A'){ //$row is from the first table } else { //$row is from the second table. } 43 minutes ago, ChenXiu said: I really want to learn how to make the, "Union Select" technique work! I suspect you might not understand what a union is for. Generally speaking, you shouldn't need to use a union. If you do, it might be a sign that you're database design needs to be re-worked. There are some reasons for it's use, but they are not all that common. I don't know your data or what you're trying to do but I'd guess that a union select probably isn't the right solution. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 1, 2023 Share Posted July 1, 2023 43 minutes ago, ChenXiu said: I am not sure how PHP echoes a dummy column Same as any other column. echo $row['columnName'); In my example above, you would echo $row('ffff'); echo $row('gggg']; echo $row['source']; 46 minutes ago, ChenXiu said: then maybe what I should do is make my first query the longest query with the most actual columns All the queries in a union must have the same number of columns, and respective columns in each must be of the same type. So you can't select an integer as the first column in one and the select a varchar as the first column in the next query. Quote Link to comment Share on other sites More sharing options...
ChenXiu Posted July 2, 2023 Author Share Posted July 2, 2023 Thank you both. My goal was actually to speed up 4 mysql queries to 4 different tables, some sharing the same data, some not. I was looking for a huge speed improvement, but with the complexity of the "union all" style, it is a nightmare for me, especially for keeping track of what data comes from which table. But I'm glad I learned, and actually executed a real "union" style query! However, I just now experimented with using "join" instead of "union" and was surprised that using "join" benchmarked in at 3 (THREE) times faster (unlike the meager 2X faster of "union") than doing normal mysql queries of 4 tables in sequence (thus extending the vacation/relaxation portion of my life by an extra 20 picoseconds, instead of 15 picoseconds (golf course, here I come!)). I'm finding mysql to be more of a religion than a science. Like Excel. A few have absolutely mastered it, and for them, mysql can do just about everything on the planet. Same with Excel. The rest of us will never truly get it.But that's what makes it fun to try and learn. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.