Is there a way I can validate a user id actually exists before I do something like relating a row in a different table to it? Like when I'm going to insert a new photo for user id #5 INSERT INTO photo (filename, caption, owner) VALUES ("pic.jpg", "vacation", 5) can I put something in the WHERE clause that will cause this query to fail if there is no user with id 5?
|
|
# ? Mar 10, 2008 03:14 |
|
|
# ? May 12, 2024 01:10 |
|
fletcher posted:Is there a way I can validate a user id actually exists before I do something like relating a row in a different table to it? In PostgreSQL it looks something like this: code:
The "ON UPDATE CASCADE" bit means that if the user ID gets updated to a different value, the photo table's user_id will be updated too. The "ON DELETE CASCADE" bit means that if a user ever gets deleted from the user table, their rows in the photo table will be automatically removed too.
|
# ? Mar 10, 2008 03:27 |
|
Orbis Tertius posted:
Try using both PHP and SQL together rather than separately. php:<? $sql = "SELECT * FROM demographics WHERE 1=1 "; if(isset($age)&&$age!='') $sql .= " AND age = $age"; if(isset($gender)&&$gender!='') $sql .= "AND gender = $gender"; mysql_query($sql); ?> noonches fucked around with this message at 05:11 on Mar 10, 2008 |
# ? Mar 10, 2008 03:28 |
|
Orbis Tertius posted:I want to have a form that lets the user search a number of variables that are in several different tables and I want the resulting table to only display columns for the fields that were filled in on the form. This is not quite so simple, because it's necessary to keep track of which tables are required for the JOINs and SELECTs. Here's a very simple example, and it helps that we always use table "a" in the query: php:<? $from_tables = array(); $selects = array(); $filters = array(); if($_REQUEST['some_b_param']) { $from_tables[] = "JOIN b USING (a.id = b.a_id)"; $selects[] = "b.*"; $filters[] = "b.foo = ".escape_sql($_REQUEST['some_b_param']); } if($_REQUEST['some_c_param']) { $from_tables[] = "JOIN c USING (a.id = c.a_id)"; $selects[] = "c.*"; $filters[] = "c.bar = ".escape_sql($_REQUEST['some_c_param']); } $sql = "SELECT a.* ".implode(",", $selects)." FROM a ".implode(" ", $from_tables)." WHERE true AND ".implode(" AND ", $filters); ?>
|
# ? Mar 10, 2008 03:34 |
|
Orbis Tertius and noonches, please stop with the table-breaking and SQL injection-vulnerable code.
|
# ? Mar 10, 2008 05:03 |
|
Victor posted:Orbis Tertius and noonches, please stop with the table-breaking and SQL injection-vulnerable code. Hey, I just threw his code in there the way he set it up, if he wants to make his queries vulnerable to injection, that's his problem. Besides, how do you know he didn't already escape the values for $age and $gender? and yeah, the table breaking was my bad
|
# ? Mar 10, 2008 05:16 |
|
Good day, I need some help on a (relatively) simple SQL query. Here's the situation: I have one table (Table1) containing an immense ammount of data (think: 100,000+ records). This table has a field called "affected_item" which containt a certain PIN, and random gibberish characters. I have a second table (Table2) which contains one column, which is a list of PINs. The goal is to find how records from Table1 contain a pin thats in Table2. Here's my query: code:
I've tried the JOIN approach, but to no avail. The problem comes from the fact that Table1's 'affected_item' field has 'RANDOM GIBERISH' + PIN + 'RANDOM GIBERISH' Thanks !
|
# ? Mar 10, 2008 20:34 |
|
Really stupid potential response, what RDBMS are you using, and are you positive that '+' is that database's operator for string concatenation? E.g. MySQL uses double pipes '||' last I checked. I'm not sure what the expected behavior is for using '+' with strings, but it might well be happily trying (and failing) to do addition with them instead, and just not informing you of such. If this doesn't apply, then all I can say is that a good troubleshooting step is to try individual parts of your code/query before trying the whole. Make sure your sub-select works as expected for good known pin/affected_item values, and only then move up to trying out the WHERE EXISTS type of stuff.
|
# ? Mar 10, 2008 23:15 |
|
Nibelheim posted:Good day, Using like can kill performance quickly. I would suggest finding a way to extract the PIN from the affected_item column. Play around wiht it a bit and see what performance different methods give you on small samples. Separating the PIN can be done through either by truncating, or built in functions like Oracle's 'TRANSLATE'. If this comes up often you might want to consider going to First Normal Form and placing the PIN in its own column.
|
# ? Mar 10, 2008 23:40 |
|
bitprophet posted:Really stupid potential response, what RDBMS are you using, and are you positive that '+' is that database's operator for string concatenation? E.g. MySQL uses double pipes '||' last I checked. I'm not sure what the expected behavior is for using '+' with strings, but it might well be happily trying (and failing) to do addition with them instead, and just not informing you of such. I wasn't sure about the '+' either, so I tried it on a mysql server. Turns out it works. I tried like queries with "LIKE '%'+somefield+'%'" as well as "LIKE CONCAT('%',somefield,'%')" and both worked. Of course that was what drew my attention first and, since it worked for me, I couldn't see an outright answer. I even have tables set up roughly the way his pin gibberish is, so I tried his whole query with just fieldnames changed, and the only conclusion I could come to was that the pins just did not match anywhere, because everything else seemed to work. Also, I've actually never seen pipes used for string concatenation in mysql before, news to me. noonches fucked around with this message at 00:14 on Mar 11, 2008 |
# ? Mar 11, 2008 00:11 |
|
var1ety posted:Run something like this. You can instantiate it as a procedure for easy reuse. This is from within SQL Plus?
|
# ? Mar 12, 2008 00:00 |
|
noonches posted:Also, I've actually never seen pipes used for string concatenation in mysql before, news to me. I believe that's in oracle. I recall seeing | used for concatenation in oracle for my school courses.
|
# ? Mar 12, 2008 04:12 |
|
Victor posted:SQL injection-vulnerable code. Being an SQL novice I've no clue what you're talking about, but it sounds bad; would you mind explaining what in particular about the code makes it injection-vulnerable? I'm pretty much teaching myself all this stuff on a case-by-case basis using the internet when I can't figure something out, and info about vulnerabilities isn't something I've looked into. noonches posted:escape the values for $age and $gender Would you mind explaining this?
|
# ? Mar 13, 2008 23:00 |
|
mysql_escape_string() or mysql_real_escape_string() to escape characters that might break your sql query. Injection is a way user input can possibly break your SQL or cause changes to your query. An example being a login, if you sql is set up like "WHERE username='$username'", and injection attack would be something along the lines of entering: " asdf' OR 1=1 OR a='a" into the username field which would cause the SQL to be: " WHERE username = 'asdf' OR 1 = 1 OR a='a' " which would evaluate to true no matter what. This is a horrible example, but should give you an idea of what injection is about. Escaping the characters causes their input to be " asdf\' OR 1=1 OR a=\'a " and the SQL should not read their input as statements. So anytime you allow user input into an SQL statement, you should escape the string. noonches fucked around with this message at 23:20 on Mar 13, 2008 |
# ? Mar 13, 2008 23:12 |
noonches posted:mysql_escape_string() or mysql_real_escape_string() to escape characters that might break your sql query. Isn't using an abstraction layer and prepared statements better to avoid the whole get_magic_quotes_gpc() disaster (even though I think it's removed in PHP6)? code:
|
|
# ? Mar 13, 2008 23:49 |
|
I think this is the thread most appropriate for this question, I have a field that has entries like "Song1,Song2,Song3,Song4" etc, and It needs to turn into a list. After trying some black magic I came up with this:php:<?php $kablooey = explode(",", $setlist); foreach($kablooey as $tracks): ?> <li><?=$tracks?></li> <?php endforeach; ?>
|
# ? Mar 14, 2008 12:37 |
|
hitze posted:Is this the best way to go about taking my field of comma seperated values and getting them unseperated, or is this going to make all the computers in the world explode 42 times? You might run into problem if you want tracks with ',' in it, it's ok otherwise. Mind if I ask why the tracks are not entered in their own table, and separate? (Assuming you have an SQL backend)
|
# ? Mar 14, 2008 15:15 |
|
var1ety posted:Run something like this. You can instantiate it as a procedure for easy reuse. Update: after getting some time to play around with this some more(i.e. not having any assignments due), I tried this out again with a / after end; (as examples on Google show). It now only complains about foreign keys not: code:
code:
code:
|
# ? Mar 14, 2008 17:26 |
|
No forward slash at the end, and a space before the word "cascade". Yes, adding "cascade constraints" will handle foreign keys.
|
# ? Mar 14, 2008 19:17 |
|
IsaacNewton posted:You might run into problem if you want tracks with ',' in it, it's ok otherwise.
|
# ? Mar 14, 2008 20:18 |
|
If you have 300 tracks, you really should have a linked table. That way might be justifiable for just a few tracks, tho not really, but having an imploded set of 300 or so means you should really be setting up a linked table. I am not sure about performance differences in the two ways, but I can almost guarantee that if you try to use a table like that in any way other than the very specific way you have it working now, you're gonna find yourself doing alot of code to get your data when you could have just added an appropriate join to the query.
|
# ? Mar 14, 2008 20:25 |
|
hitze posted:I figured it would be easier if its in the same table as the rest of the information for each set, theres gonna be around 300 of them. I don't know which way is more efficient for the dater-base The database should be as if not more efficient than whatever code you'll end using to split the string. Use the database the way it's meant to be used (as in, you have a one to many relationship there, so link the tables as such and use an independent query to get the album's tracks)
|
# ? Mar 14, 2008 20:33 |
|
So what you're saying is make a new table, and then make a field for each track? or the same one field with commas seperating it? Just to make sure i'm understanding you
|
# ? Mar 14, 2008 20:44 |
|
Whats the easiest way to get a total of a column by calendar months? For instance, since today is 3-14-2008, I need the total from the last 6 calendar months, so that would be all items from 9-01-2007 to 3-14-2008.
|
# ? Mar 14, 2008 20:45 |
|
hitze posted:So what you're saying is make a new table, and then make a field for each track? or the same one field with commas seperating it? Just to make sure i'm understanding you No, you might want to read some sort of SQL primer. Your album will need an unique identifier (ID) of some sort. From that, you can create the track table, each track having a reference to the unique identifier of the album. That way you can SELECT all the track WHERE the unique identifier is something specific.
|
# ? Mar 14, 2008 20:49 |
|
nbv4 posted:Whats the easiest way to get a total of a column by calendar months? What DBA are you using? In MySQL, you could GROUP BY year(date), month(date) (e.g. the two fields) example: code:
|
# ? Mar 14, 2008 20:55 |
|
IsaacNewton posted:What DBA are you using? yeah, I'm using mysql and that query you provided works wonderfully. Is there any way to get it to return it it all on one row, though? edit:" nevermind, i figured it out. remove the GROUP BY part... nbv4 fucked around with this message at 00:37 on Mar 15, 2008 |
# ? Mar 14, 2008 22:58 |
In MySQL is there a way to do a SELECT but have it omit rows where the value in a column is within a certain range of the previous row it returned? Like if I have a table with a timestamp column and I want to return rows that are atleast 10,000 seconds apart or something. Does that make sense? I'm terrible at trying to explain things.
|
|
# ? Mar 15, 2008 00:41 |
|
fletcher posted:In MySQL is there a way to do a SELECT but have it omit rows where the value in a column is within a certain range of the previous row it returned? Like if I have a table with a timestamp column and I want to return rows that are atleast 10,000 seconds apart or something. Does that make sense? I'm terrible at trying to explain things. What you're looking for is something called an analytic function. Oracle (and I think now certain versions of MS SQL server) have these but to my knowledge MySQL does not. Specifically, Oracle provides SELECT foo, LAG (foo, 1) OVER (ORDER BY blah) FROM ... which will let you access data from a "window" of data. These are sometimes called "windowing" queries. However, the last time this came up I did find this page which might have a passable way to fake it.
|
# ? Mar 15, 2008 00:57 |
yaoi prophet posted:However, the last time this came up I did find this page which might have a passable way to fake it. Interesting. Thanks for the information. That looks like a pretty messy query. I think I'm just gonna do it with PHP (if $timestamp > $lastTimestamp+x) { do stuff }) when I spit out the data as performance isn't really an issue for the page I need it for.
|
|
# ? Mar 15, 2008 02:14 |
|
Can someone help me with this indexing problem (mySQL 5.1)? I can't seem to get this particular query to index (always is using filesort on the thoughts table), and it's bugging the hell out of me so much that the first person who can resolve this for me, I'll paypal $20 to.code:
code:
code:
|
# ? Mar 15, 2008 08:31 |
|
Lt Moose fucked around with this message at 05:37 on Apr 4, 2016 |
# ? Mar 15, 2008 19:37 |
|
var1ety posted:No forward slash at the end, and a space before the word "cascade". Yes, adding "cascade constraints" will handle foreign keys. Excellent, this worked very nicely. Thanks for the help.
|
# ? Mar 15, 2008 20:19 |
|
Lt Moose posted:The first one is this line isn't working. It does not error, but it does not update the row. The @ sign should be suppressing any errors, if I remember correctly. Edit: also, sanitize your input. noonches fucked around with this message at 20:49 on Mar 15, 2008 |
# ? Mar 15, 2008 20:36 |
|
I have a SQL problem that I can't even figure out where to begin. I need to get the date of the third from last occurance of a record. Say this is the table:code:
|
# ? Mar 17, 2008 07:34 |
|
nbv4 posted:I have a SQL problem that I can't even figure out where to begin. I need to get the date of the third from last occurance of a record. Say this is the table: code:
But that won't work in all cases: for example if you wanted the 5th to last record (ID 1 in my example), you'll get nothing back because there's no total with id 5. What you want is the row where the total is either the position you want, or failing that, the the next number after that. So let's tweak the SQL again: code:
|
# ? Mar 17, 2008 08:11 |
|
minato posted:Bingo. so if "item" is "approaches", and the name of the table is "flights" would the query look like this: code:
|
# ? Mar 17, 2008 08:30 |
|
You might want to order by total, then by date, to avoid cases where there are some dates with 0 approaches.
|
# ? Mar 17, 2008 08:33 |
|
I have a date related problem. There is table of downtime ranges, they are DateTime - i.e. code:
(based on the table above) code:
(quick pseudocode) code:
|
# ? Mar 17, 2008 08:35 |
|
|
# ? May 12, 2024 01:10 |
|
Charun posted:I have a date related problem. I think this will work, but it's just off the top of my head: code:
|
# ? Mar 17, 2008 13:04 |