Jump to content
Larry Ullman's Book Forums

Recommended Posts

Hi all,

I have a table that looks like this:

 

category_artist_id---------category_id-----------artist_id

-------39------------------------------7-----------------------22-----

-------40------------------------------11----------------------23----

-------41------------------------------11----------------------24----

-------42------------------------------11----------------------25---

-------43------------------------------11----------------------26---

-------44------------------------------9-----------------------26---

 

I already know the category_id, in this case 11 as the user has picked thisfrom a form. I want to write a query that would return the bottom row (44), i.e. one that returns a cat_artist_id where an artist_id has more than one category_id. In this case artist_id 26 has another category apart from 11 (it's 9). The idea is that artist_id 26 won't be deleted because they use another category as well as 11.

 

Even as I write this I think that this must be simple but I can't get it to work in 1 query. Basically what I want to say is:
 

"select artist_id from this table where category_id = 11 THEN using these artists_id's select artist_id where category_id does not equal 11".


It looks above like I need 2 queries to get the result I want. Does this indicate that I've not structured my tables correctly.


Should it be this complicated?

 

Thanks

Paul

 

Link to comment
Share on other sites

There may be multiple solutions to this, but my initial thought (without being able to test my query) is that a subquery in the WHERE clause will get you what you want. For example:

 

SELECT artist_id FROM this-table WHERE category_id != 11 AND artist_id IN (SELECT artist_id FROM this-table WHERE category_id = 11);

I apologize in advance if that doesn't work, but it *feels* like it should.

  • Upvote 1
Link to comment
Share on other sites

Hartley San,

Brilliant. I won't get a chance to test it until later but I didn't realise that you could do a select within a select, that's the answer.

 

Would I be correct in thinking that the second SELECT (the one in brackets) is done first and then the IN conditinal says 'take these values only' and applies them to the first SELECT.

 

This is one that's definately worth remembering.

 

Cheers

Paul

PS No need to apologise I really appreciate the help even if it doesn't work.

Link to comment
Share on other sites

I built a quick table with your data, and my suggested query does in fact work. (Phew! Lucky me.)

 

To answer your question, yes, the subquery (as it's called) in the parentheses is executed before the outer query.

In essence, the subquery is run first, creating a temporary table in memory, which is then used to perform the outer query.

 

Something that's not so obvious though is that the IN clause is actually fairly inefficient, and it's much more efficient to use the EXISTS clause instead. (In fact, MySQL may very well rewrite an IN-clause query to an EXISTS-clause query behind the scenes without you knowing it to speed things up.)

 

For my test, my table name was "t", and I used the following IN-clause query:

SELECT * FROM t WHERE category_id != 11 AND artist_id IN (SELECT artist_id FROM t WHERE category_id = 11);

That query can be rewritten to the following EXISTS-clause query, which produces the same result and is faster:

SELECT * FROM t AS t1 WHERE category_id != 11 AND EXISTS (SELECT 1 FROM t AS t2 WHERE category_id = 11 AND t1.artist_id = t2.artist_id);

Note that I used aliases for both of the tables so that I could essentially create two copies of the same table in memory, and perform the comparisons that way.

Hope that's useful.

 

Also, here are some links that may be of use:

http://dev.mysql.com/doc/refman/5.6/en/subquery-materialization.html

http://www.jortk.nl/2008/07/exists-much-faster-then-in-in-mysql/

 

I don't know the details, but apparently with the IN clause, the subquery table is recreated in memory for each "hit" on the outer table, making it much less efficient that the EXISTS clause.

  • Upvote 1
Link to comment
Share on other sites

  • 4 weeks later...

Hi all,

I've just got back from holiday and am trying to use HartleySan's ideas above in a realworld query and the error I'm getting is that of an invalid use of the GROUP clause. To summarise what I'm trying to do.

 

Firstly I want to select the artist_id from cat_artist table where the category_id equals the category to be removed. The user has chosen the category to be removed and it's stored in a session. This is the subquery in the statement below. The sub query works because I tested it seperatly first.

 

Then I want to take these artists_ids ONLY and select the artist_ids that belong to only 1 single category_id (these are the ones to be removed, as opposed to those that belong to mutiple categories, which don't). So I only want artist_ids that belong solely to the category that needs to be removed.

 

Once I have the correct artist_ids in an array I plan to use a foreach loop to iterate through them and delete them. But I need the correct artists in there in the first place.

 

I tried the following:

 

$q = "SELECT artist_id FROM cat_artist WHERE COUNT(category_id = 1) AND EXISTS (SELECT artist_id FROM cat_artist WHERE category_id = {$_SESSION['category_id']})";

 

The error message is an invalid use of the GROUP clause. My guess is that I'm using the COUNT syntax incorrectly.

 

Any help would be really great.

 

Thanks

Paul

 

PS. Can anyone recommend an SQL resource. I tried the MYSQL website but I find it a bit heavy and technical. Something like the PHP manual, which is a bit more 'readable' would be ideal.

Link to comment
Share on other sites

Paul, the reason you're getting an error is because you cannot use an aggregate function (e.g., COUNT) in the WHERE clause.

To do essentially that though, you need to use the HAVING clause, which also requires the GROUP BY clause, thus the error you're getting.

 

The actual query you need to solve your problem is a bit tricky. I'm used to fairly complex queries, and even so, it took me a bit to find a query that works.

One technique that I've found that works a lot for complex SQL queries is to essentially use a double negative.

 

For example, in this case, you are given a category ID, and you want to find all the artist IDs associated with that category ID that are not associated with other category IDs, right?

If we were to flip that around using a double negative, we'd get something like the following:

Find all artist IDs that are not associated with a category ID that is not the category ID we're interested in.

 

You may have to read that a few times to get where I'm coming from, but that's essentially the logic we need to write a query for.

So now, let's generate the query one piece at a time (as this is how I always go about generating complex queries that contain subqueries and the like).

 

First off, let's get all the artist IDs that are associated with a category ID that is not the category ID we're interested in. For the time being, I'm going to assumed that we're always interested in the fixed category ID 11 (and we'll add the session variable into the mix at the end). Here's the query with a category ID of 11:

SELECT artist_id FROM cat_artist WHERE category_id != 11;

 

I think that's pretty straightforward.

Now the next part is to make the above into a subquery that we search upon by saying, "Give me all the artist IDs that are not a part of the returned results." We can do that as follows:

SELECT artist_id FROM cat_artist WHERE artist_id NOT IN (SELECT artist_id FROM cat_artist WHERE category_id != 11);

 

That there is essentially the answer to your question, and should get you the artist IDs you're interested in (i.e., only the artist IDs that are associated with the category ID you're querying and not other category IDs).

With that said, as I mentioned in a previous post, the IN clause is not very efficient, so we can rewrite the above to the following using NOT EXISTS instead:

SELECT artist_id FROM cat_artist AS c1 WHERE NOT EXISTS (SELECT artist_id FROM cat_artist AS c2 WHERE category_id != 11 AND c1.artist_id = c2.artist_id);

 

And finally, we can wrap all this up into a PHP string with the session variable as follows:

$q = "SELECT artist_id FROM cat_artist AS c1 WHERE NOT EXISTS (SELECT artist_id FROM cat_artist AS c2 WHERE category_id != {$_SESSION['category_id']} AND c1.artist_id = c2.artist_id);";

 

That should get you what you want.

Please note that I ran the query through phpMyAdmin, and it worked fine, but I did not run it through a PHP script, so there is always the possibility that I made a mistake with my syntax.

All the same, I think the above should be enough to get you what you want.

 

As for good MySQL resources, I don't really have any.

One thing I can say though is that 99.9% of the time you have a MySQL question, if you just search on "SQL" instead of "MySQL", you should find a relevant answer, as the syntax should be the same for both.

Generally, instead of having a go-to resource for SQL questions, I generally just Google what I'm looking for, and a vast majority of the time, the first hit will be a relevant Stack Overflow question/answer.

 

Lastly, looking at your table structure presented in the first post, you can probably drop the category_artist_id column. The reason being that it would seem that every combination of category_id and artist_id in the table is already unique. As such, you can drop the category_artist_id column and then set up a unique multicolumn index on the category_id and artist_id columns.

 

Please let me know if you have any follow-up questions.

Thanks.
  • Upvote 1
Link to comment
Share on other sites

+1 HartleySan. I was looking at this earlier and playing around (unsucessfully) with queries such as 

SELECT artist_id FROM cat_artist as t1 WHERE artist_id IN (SELECT artist_id FROM  cat_artist as t2 WHERE category_id = 11)  
GROUP BY category_id HAVING COUNT(category_id) = 1

I'm surprised there is not a way to structure the query in such a way to make it more legible and still get the desired results. Perhaps though I'm not understanding  how complex queries work.

Link to comment
Share on other sites

margaux, I think your understanding of complex queries is sound, but I also thing that "legible" is a very subjective term when it comes to SQL queries.

 

In this particular case, after thinking about multiple possible queries, it occurred to me that the "double negative" approach, while perhaps the least obvious, actually makes for the shortest and simplest (and likely the fastest (subject to testing, of course)) query.

 

I think your approach of getting all the artist IDs for the given category ID and then counting the number of instances for each of those artist IDs is also a possibility. If you went that route, then I think you would want to use the following query:

 

SELECT artist_id from cat_artist WHERE category_id = 11 AND artist_id IN (SELECT artist_id FROM cat_artist GROUP BY artist_id HAVING COUNT(artist_id) = 1);

 

(I did not test the above query, but it's something like that.)

Again though, I felt like the double negative query was the shortest and quickest, which is why I recommend it.

Link to comment
Share on other sites

Hiya,

The following worked a treat:

$q = "SELECT artist_id FROM cat_artist WHERE artist_id NOT IN (SELECT artist_id FROM cat_artist WHERE category_id != {$_SESSION['category_id']})";

 

The NOT IN conditional and the concept of subqueries was the key for me with this question, which is essentially what the 'double negative' approach used. A good learning curve, this one. Thanks for that.

 

However when replacing the IN part with EXISTS, as follows:

$q = "SELECT artist_id FROM cat_artist WHERE NOT EXISTS (SELECT artist_id FROM cat_artist WHERE category_id != {$_SESSION['category_id']})";

 

It didn't error, it just didn't return anything.

 

Thanks for all your help.

Paul

Link to comment
Share on other sites

Paul, your second query didn't work because it's not complete. Please look at my NOT EXISTS query one more time, and you'll hopefully see what you're missing.

Also, I recommend looking at my second post in this thread, as I get into IN vs. EXISTS in more detail in that post.

Link to comment
Share on other sites

My apologies HartleySan, it appears that aliases are more significant than I gave them credit for. I thought that the assignment of aliases was purely a cosmetic renaming.

 

I note from your post above that aliases create tables in memory. However what's the purpose of the 'AND c1.artist_id = c2.artist_id' part? At first glance if the subquery is performed first then c1 would be unknown, as that's not created until the second part of the query.

Link to comment
Share on other sites

"legible" is a very subjective term when it comes to SQL queries.

HartleySan - very true!

 

Your query works nicely and is 'legible'. I had started thinking that I should be counting artist_id instead of category_id but I will need to give it some more thought to understand why counting category_id was not coming up with the desired result.  I do agree that your approach using double negatives is faster. 

 

There's a good explanation of the different sorting approach sql uses for WHERE and HAVING in the SAMS sql book.

 

 

 

 

Link to comment
Share on other sites

Hiya,

HartleySan. When you have time I would be interested in trying to understand why it's needed for the EXISTS but not the IN. The IN syntax makes perfect sense to me where the EXISTS syntax doesn't. But please don't spend ages as you've spent a lot of time on this already.

 

Margaux. I've just found the Sams book on Amazon. As per the discussion above, in the abscence of a good on-line resource would you recommend it as a good old-fashioned paper reference? It's £31, but it's worth it if it's good. Probably a silly question but I assume that SQL syntax from an SQL book is the same syntax as used in MySQL in a PHP query?

 

Has anyone else used it?

 

Cheers

Paul

Link to comment
Share on other sites

Okay, Paul, I'm going to try to explain this as best I can. If something isn't clear, please let me know.

For all the examples in this post, I'm going to use the following data, which is essentially your original data minus the unnecessary category_artist_id column and the names of the two remaining columns shortened for ease of reference:
 
cid          aid
  7           22
 11           23
 11           24
 11           25
 11           26
  9           26
 
Now first, a little background information.
The EXISTS clause (not surprisingly) checks for the existence of something. Specifically, it runs the subquery and if even a single row in the subquery table meets the conditions in the WHERE clause of the subquery, then the "EXISTS (subquery)" part evaluates to TRUE. It's important to note that no actual values are returned from the subquery. Conversely, if no rows in the subquery table meet the subquery conditions, then the "EXISTS (subquery)" part evaluates to FALSE.
What this essentially boils down to is that if "EXISTS (subquery)" is the only thing in the WHERE clause of the outer query and it returns TRUE, then the corresponding row in the outer query is returned. Otherwise, the row is not returned.
 
Something important to note about both EXISTS and IN is that you essentially have to loop through every row in the subquery table for each row in the outer query table. This can lead to a performance hit with big tables.
 
However, the reason EXISTS is generally more efficient than IN is because the EXISTS clause immediately ends upon a single row in the subquery meeting the subquery conditions. As a result, the subquery often does not have to loop through every single row in the subquery table before it terminates. This often leads to a huge performance boost over IN.
 
However, NOT EXISTS is not the same. Because of the way NOT EXISTS works, it has to loop over every row in the subquery table before it can be evaluated. As such, the performance gain that EXISTS has over IN is more or less lost with NOT EXISTS (I was not aware of this myself until I researched it the other day).
The reason NOT EXISTS has to go through each row in the subquery is because NOT EXISTS cannot evaluate to TRUE or FALSE until every row in the subquery table has been gone through and it can conclusively say that, "Yes indeed, there is not a single row that meets the conditions in the subquery, therefore TRUE can be returned."
 
Some other things to note, because only TRUE or FALSE is returned for "EXISTS (subquery)", it doesn't matter what's in the SELECT clause of the subquery. In my post above, I wrote "SELECT artist_id" for the subquery to avoid any further confusion, but it doesn't matter. In fact, most people write either "SELECT *" or "SELECT 1" for the subquery. It really doesn't matter.
 
Something else to note, whenever you want to join the same table to itself (which we're essentially doing in the subquery), you have to create an alias for each table, or else the query parser has no way of knowing what's referring to what table, etc., etc. However, if a subquery is accessing a different table from the outer query, then you do not have to use aliases (although people often do anyway because it's just easier to understand).
 
Lastly, a note on vocabulary. When a subquery references the table in the outer query, the subquery is called a correlated subquery. The opposite (i.e., a subquery that does not reference the outer query) is called a noncorrelated query.
For correlated queries, the outer query must be analyzed first in order to provide the proper value(s) to the subquery. In a noncorrelated subquery, the subquery is analyzed first, because it is not dependent on values from the outer query.
 
In the queries I provided, all the subqueries are correlated subqueries, meaning that the outer queries are evaluated first, and provide the appropriate values to the subquery.
Correlated subqueries are slower than noncorrelated subqueries, and as such, while there are a ton of variables involved in "which query is faster than another" (e.g., "Is it the third Tuesday of the month and is the wind moving in a northeasterly direction while I have my finger on my nose?!"), I'm starting to question my original statement that the NOT EXISTS query is faster than the NOT IN query because of the two points stated above. In other words:
1) NOT EXISTS must go through every row in the subquery table.
2) Correlated subqueries are slower than noncorrelated subqueries.
 
Truth be told though, you likely wouldn't be able to see a difference between the two queries unless you had at least 10,000 rows in both the outer query table and the subquery table (in this case, the same table).
Furthermore, because the MySQL query optimizer tries to take all queries and turn them into faster queries behind the scenes, for all I know, all the different queries I recommended might ultimately be turned into the same query that is actually executed on the DB.
 
Point being, without exhaustive testing, I couldn't tell you which query is faster, but I guess so long as the query works, that's good enough in this case.
In general though, joins are faster than both EXISTS and IN, so if you can reform your query into a join, you'd probably be best off. (I haven't bothered thinking about how to turn your query into a join; for another day, I suppose. Also, there are cases in which (NOT) EXISTS cannot be turned into a corresponding JOIN query.)
 
Now, onto an explanation of what happens when you remove the " AND c1.artist_id = c2.artist_id" part from the following query:
SELECT aid FROM cat_artist AS c1 WHERE NOT EXISTS (SELECT aid FROM cat_artist AS c2 WHERE cid != 11 AND c1.aid = c2.aid);
 
In general, (NOT) EXISTS always requires a correlated subquery (e.g., "c1.aid = c2.aid") for reasons that will hopefully be clear in a second.
For the examples, let's first simply things by changing the above query around a bit to the following:
SELECT aid FROM cat_artist AS c1 WHERE EXISTS (SELECT 1 FROM cat_artist AS c2 WHERE cid = 11);
 
First off, I removed the " AND c1.aid = c2.aid" part. Also, I changed NOT EXISTS to EXISTS and != to =. I did this because the double negative can be a bit mind-bending at first. (Also, I changed "SELECT aid" to "SELECT 1", but that doesn't matter.)
 
Now, let's run the sample table data above on this query.
Because this is a noncorrelated subquery, the subquery will be run first.
The subquery table will be gone through one row at a time, and because the second row in the table has a cid of 11, the subquery will return that row, immediately causing EXISTS to evaluate to TRUE, and thus cause the corresponding row in the outer table (in this case, the first row) to be returned.
The next row (the 2nd row) in the outer table will be processed, and the exact same result will occur (i.e., the second row in the subquery table will be returned, causing EXISTS to evaluate to TRUE).
This process will continue for each row in the outer table, ultimately resulting in EVERY row in the outer table being returned.
 
Now let's see what would happened if we changed the query above so that the only change is EXISTS to NOT EXISTS:
SELECT aid FROM cat_artist AS c1 WHERE NOT EXISTS (SELECT 1 FROM cat_artist AS c2 WHERE cid = 11);
 
In this case, the NOT EXISTS (subquery) part will only evaluate to TRUE if there are no rows in the entire subquery table with a cid of 11.
However, because there are four rows in the subquery table with a cid of 11, the NOT EXISTS (subquery) part will ALWAYS evaluate to FALSE, thus causing no rows to be returned by the outer query.
This is why the original query you ran was always returning zero rows. This is also why (NOT) EXISTS pretty much always needs a correlated subquery to further filter the rows evaluated by the subquery.
 
So let's look at the EXISTS query again with the correlated subquery part added back in as follows:
SELECT aid FROM cat_artist AS c1 WHERE EXISTS (SELECT 1 FROM cat_artist AS c2 WHERE cid = 11 AND c1.aid = c2.aid);
 
Because this is a correlated subquery, we have to start with the outer query. So we start with the 7-22 row. Because the aid of both tables have to be the same to return a row for the subquery, the only time the c1.aid = c2.aid condition will be met is when the 7-22 row is also being checked for the subquery table. However, because the cid of the 7-22 row is 7 (and not 11), then that condition will fail, thus causing the EXISTS to evaluate to FALSE for the 7-22 row in the outer query (and thus not return it).
To save time and space, you can run through all the other rows, and you will see that for all the rows with a cid of 11, EXISTS will evaluate to TRUE for them.
In other words, rows 2, 3, 4, and 5 will be returned with their corresponding artist IDs.
 
Now, lets flip this all back around to the query with the double negatives in place as follows:
SELECT aid FROM cat_artist AS c1 WHERE NOT EXISTS (SELECT 1 FROM cat_artist AS c2 WHERE cid != 11 AND c1.aid = c2.aid);
 
Using our sample data again, the NOT EXISTS ... cid != 11 part by itself will always evaluate to FALSE as we discussed above (because there is always at least one row in the table that has a cid of 11), but if we add that condition to the c1.aid = c2.aid, then we get a "better" result.
 
Because of the c1.aid = c2.aid condition, only the following rows are ever even compared:
Outer table          Subquery table
       7-22                   7-22
 
      11-23                  11-23
 
      11-24                  11-24
 
      11-25                  11-25
 
      11-26                  11-26
      11-26                   9-26
 
       9-26                  11-26
       9-26                   9-26
 
And of these evaluated rows, the only ones in which a cid other than 11 does not exist (in other words, ONLY a cid of 11 exists) are for 11-23, 11-24 and 11-25 in the Subquery table column. Note that this is not true for the 9-26/11-26 groupings, because even though one cid is 11, the other is not 11, and NOT EXISTS require that all rows meet the conditions to evaluate to TRUE.
As such, only artist IDs of 23, 24 and 25 are returned for the query.
 
Okay, that was a really long and convoluted explanation, but I can't think of a better way to explain it.
If my above explanation made no sense (which wouldn't be surprising), my best advice is to start with simpler queries, walk through them one row at a time until they make sense, and then move up to the harder ones.
 
As always, ask questions if you have any, but as is hopefully pretty clear after this post, I can't explain the logic behind queries very well.
  • Upvote 2
Link to comment
Share on other sites

Hi Paul - difficult to say if I would recommend the SAMS book purely because I have I got it from the library. I've yet to spend alot of time with it but first look looks good and comprehensive.

 

I assume that SQL syntax from an SQL book is the same syntax as used in MySQL in a PHP query?

Not a silly question - the syntax for mysql and sql whilst very similar do have some differences. For example there is a cube function available in sql but not in mysql. Let me know if you have any specific questions and I'll try to read up on it.

Link to comment
Share on other sites

Thanks, Antonio.

When Paul asked the original question, I knew the explanation would be long and I debated even bothering to try to answer it, but I had a little free time the other day, so I decided to just buckle down and attempt to answer it anyway.

However, after not too long into writing a reply, my answer started to get really long and confusing, but I figured, "What the heck?! I might as well just post my reply anyway."

At the very least, I figured that if any parts of my reply weren't clear, Paul could just come back later and ask about them, so I left it as is (i.e., long and confusing).

Link to comment
Share on other sites

Wow! This is marvellous, thanks for your work on this. This morning I had a good read, and then another good read. Then I took myself out for a walk to clear my aching head and then had another good read!

 

I think I've got most of it OK. The fact that I don't have all of it says more, of course, about my powers of reasoning that about your very clear explanation.

 

My thoughts as I've gone through it.

 

1. The fact that the subquery returns no values, only TRUE or FALSE never occurred to me. Seems obvious now you've explained it but my original query would never have worked because I was expected the outer query to check on actual values returned from the subquery.

 

2. I can see clearly now why an alias is needed for some but not all.

 

3. Your point about correlated and non-correlated is an important one to remember, so as to get the order of the query correct. However I'm not clear on the distinction between the two. You started with this shortened query, which you stated is a non-correlated one.

 

SELECT aid FROM cat_artist AS c1 WHERE EXISTS (SELECT 1 FROM cat_artist AS c2 WHERE cid = 11);

 

You then moved onto this query, which becomes a correlated one:

 

SELECT aid FROM cat_artist AS c1 WHERE EXISTS (SELECT 1 FROM cat_artist AS c2 WHERE cid = 11 AND c1.aid = c2.aid);

 

Now both subqueries refer to cat_artist, the table used in the outer query. That appears to me to satisfy the definition of a correlated, as per

 

'When a subquery references the table in the outer query, the subquery is called a correlated subquery. The opposite (i.e., a subquery that does not reference the outer query) is called a noncorrelated query.'

 

I'm sorry, I'm missing something. This part seems too important not to understand as it changes the order of the query.

 

Other than that above I can follow everything you have said. Your points about the 'c1.aid = c2.aid condition' are very clever, not sure my brain could make that leap of logic quite yet!

 

Thanks for your time on this, it is really appreciated.

 

Paul

Link to comment
Share on other sites

Yeah, the correlated subquery vs. noncorrelated subquery thing is kind of confusing in this case because the table referenced in the outer query is the same as the table referenced in the subquery. As such, let's use a different example:

 

Let's imagine the following query, which contains a subquery:

 

SELECT * FROM audits AS a WHERE EXISTS (SELECT 1 FROM non_tax_paying_bums AS b WHERE b.id = a.id);

 

The above is a correlated subquery because the subquery is referencing the id column in the a (i.e., audits) table. As a side note, the above query is a very contrived example as just about anyone would (rightly so) write it instead using the following join:

 

SELECT a.* FROM audits AS a, non_tax_paying_bums AS b WHERE a.id = b.id;

 

But I digress. Anyway, if we were to remove the "b.id = a.id" part from the first query (and change the subquery slightly for no particular reason), we'd get the following, which contains a noncorrelated subquery, because the subquery is no longer referencing any columns in the outer query table:

 

SELECT * FROM audits AS a WHERE EXISTS (SELECT 1 FROM non_tax_paying_bums AS b WHERE b.id = 12);

 

Please also note that because the table in the outer query is different from the table in the subquery, we don't actually need aliases, and could instead rewrite the first query to the following:

 

SELECT * FROM audits WHERE EXISTS (SELECT 1 FROM non_tax_paying_bums WHERE id = audits.id);

 

Note that just "id" (not "audits.id") at the end of the subquery refers to the id column in the non_tax_paying_bums table.

Does that answer your questions?

 

Also, I can totally relate to what you said about reading it once, taking a walk, reading it again, taking another walk, and then reading it a third time.

When I come up against something new and tricky, I quite often have to walk around alone for a while or exercise, or do something to clear my head. After that, I can usually (slowly) piece things together.

And believe me, tricky queries like the ones we're talking about are not easy, but once you get them, you get them. I don't know how the human brain works, but if you keep at it, trying different things from different angles, suddenly and without warning, the one little thing you've been missing the whole time will hit you like a bolt of lightning out of the blue and you'll be left sitting there thinking, "That's so obvious! Why did it take me so long to get that?!"

 

But again, I digress. I just simply wanted to say that I totally know where you're coming from, and if you stick with it, you'll get it.

Link to comment
Share on other sites

  • 2 months later...

Hi,

 

I'm having a similar problem and I'm not sure if it is a syntax issue or not.

What I'm trying to do is create a variable from an array that will be used in an insert statement.

 

a var_dump tells me that the $coun variable (country_id) is outputting correctly as an integer, so I'm not sure if there is no match being made because of the syntax or if something else is going wrong.

 

phpmyadmin says my query is fine.

 

Also, am I using the mysqli_real_escape_string correctly?

 

here is the code:

$q = "SELECT short_name FROM country WHERE (country_id='".mysqli_real_escape_string($coun)."')"; 
			$r = mysqli_query($dbc, $q);
			$num = mysqli_num_rows($r);
			if ($num == 1) {//match was made
				
				//Get short_name
				$row = mysqli_fetch_array($r);				
			}else {
				echo '<p>no match</p>';
				}
Link to comment
Share on other sites

I think you're not getting a match because you have put the country_id variable between single quotes so mysql treats it as a string. I would be inclined for maintainability to run the variable through mysqli_real_escape_string outside the query.

$country_id = mysqli_real_escape_string($coun);
$q = "SELECT short_name FROM country WHERE country_id = $country_id";
Link to comment
Share on other sites

hmm. That did not work.

 

this is the var_dump for the last attempt - int(246)

here is the error - no match

 

Why is it better to have the mysqli_real... outside of the query? Just curious.

 

Taking a walk. Need to clear my head.

Link to comment
Share on other sites

 Share

×
×
  • Create New...