visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: simple 2 table query
By: yapsing U.S.A.  Date: 09/06/2003 00:00:00  English  Points: 50 Status: Answered
Quality : Excellent
i have topic(primary key=id) and comments(foreign key=topic) table, so ID=TOPIC. I need to delete data from both table with those keys. Can someone help by giving my the query? i'm using JSP and mysql 2.23.56
By: VGR Date: 09/06/2003 05:28:00 English  Type : Comment
you can't do multitable updates nor deletes with current mysql

build a loop in jsp
By: yapsing Date: 09/06/2003 05:49:00 English  Type : Comment
i treated the value of date as a string where i have to join 3 items like this:String date= day1+mth1+yr1;
is it possible if i use
SELECT id from topics where date_format(date,'%d%m%Y')=date

and the use following statement:

while RS.next()
id = RS.getInt("id");
delete from topics where id=id
delete from comment where topic=id

if yes, should i type
ResultSet RS= stmt.executeQuery("select id from m_ann where date_format(postdate,'%d%m%Y')=' " + date + " ' ");
OR
ResultSet RS= stmt.executeQuery("select id from m_ann where date_format(postdate,'%d%m%Y')="+date);?
If not please explain other ways.

thank u
By: VGR Date: 09/06/2003 05:55:00 English  Type : Answer
yes, the formatted date (as any string value) has to be enclosed in single quotes [that's your first version]

and yes, use those statements to actually delete linked data :
delete from topics where id=id
delete from comment where topic=id



Now to get the id above, you don't need to date_format() and such if you use this :
String date= day1+mth1+yr1; // this you already have
String mysqldate=yr1+'-'+mth1+'-'+day1; // this you should add

query="SELECT id from topics where date='"+mysqldate+"';";

Side note : it's legible but a bad idea to use column names DATE and TIME ; those should be reserved SQL keywords. I don't know why they escaped that fate, given DATETIME, TIMESTAMP etc are.
By: yapsing Date: 09/06/2003 18:11:00 English  Type : Comment
i tried your code the date came out ok.
i did the select part too. no errors but i tried to display the id using
<% while(RS.next()){%>
<%=id%>
<%}%>
nothing came out.
any idea?
By: VGR Date: 09/06/2003 18:19:00 English  Type : Comment
echo the $query to the screen before querying, then check manually in the DB if it was supposed to return any rows :D
By: yapsing Date: 09/06/2003 18:25:00 English  Type : Comment
???? i don't get it.
what is 'echo the $query'. I did checked manually the DB where it should display 'id'

forgive me of my 'slow-ness'. my inteligent level could cause u to womit blood.
By: VGR Date: 09/06/2003 18:30:00 English  Type : Comment
printf(%s',query); ?
By: yapsing Date: 09/06/2003 18:52:00 English  Type : Comment
i'm not good at java, servlet or JSP. anyway, i checked the query again and rearranged the
String mysqldate=yr1+'-'+mth1+'-'+day1;
can you teach me how to display it in html instead.
i displayed the date using <%=mysqldate%>.
by executing the query, i get the amount of IDs but it only displayed 0s
By: VGR Date: 09/06/2003 20:34:00 English  Type : Comment
ok

1) I know nothing of JSP or such, so I guess the <%=mysqldate%> is the way it puts in the HTML response the value of the variable :D
2) was it the correct date ?


3) in fact, the only reason why you got records returned (the while() does indeed print multiple lines) but "id" empty IS MANDATORY BECAUSE 'id' doesn't exist in the columns of the table ;-))

4) do a DESCRIBE yourtable; or check manually that the field/column 'id' does indeed exist and is not empty.

I see no other possible reason...
By: yapsing Date: 09/06/2003 20:57:00 English  Type : Comment
i know clearly of what i'm doing and i do have a ID field in the table. Its declared to hold INT value. ID is not empty.
i think it must be something wrong with the table. i did a manual select or a query check whatever u call it,
the query executed successfully but nothing came out.
this is what i type:

select id from TABLE where date<=18-04-2003
it is suppose to return a table displaying a list of IDs, it display nothing but it executed successfully
and then i typed
select id from TABLE where date<=20030418
it display the ID just like what i want.
any idea why?
if u are too tired to answer this question we can wait til your time, around evening. Its 7pm here in malaysia.

i think i can take it from here....are you womitting blood yet? ;)
By: yapsing Date: 09/06/2003 21:00:00 English  Type : Comment
PROBLEM SOLVED! THANKS FOR THE TIME. i FOUND THE BLOODY PROBLEM.
By: VGR Date: 09/06/2003 21:16:00 English  Type : Comment
you forgot the single quotes around the date value...

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 359.335180 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page