requirements changed in join...now...how to do it?


Hi

I have 2 tables, faq and faq_categories...i have a join that work, and so far, I was a happy camper.

But...requirements change, and i have to change the join, but i don't know how to do it

Here is the current code that works just fine:

Code: SELECT faq.* , faq_categories.categoryname FROM faq JOIN faq_categories ON ( faq.catid = faq_categories.catid) So far, all faq belongs to one category...but from now on, there will be faq which will not belonng to any category....and that complicate things, at least for me.

How should I change this code in order to display the faq which does not have catid

Here are my tables:

Code: CREATE TABLE IF NOT EXISTS `faq_categories` ( `catid` int(11) NOT NULL AUTO_INCREMENT, `parentid` int(11) DEFAULT NULL, `categoryname` varchar(255) NOT NULL, `categoryname_en` varchar(255) DEFAULT NULL, `description` text, `description_en` text, `metatags` text, `metatags_en` text, `sorder` int(11) NOT NULL, `visible` tinyint(4) NOT NULL, `categoryphoto` varchar(255) DEFAULT '', PRIMARY KEY (`catid`), KEY `parentid_fk` (`parentid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=204 ; CREATE TABLE IF NOT EXISTS `faq` ( `faqid` int(11) NOT NULL AUTO_INCREMENT, `catid` int(11) DEFAULT NULL, `question` text NOT NULL, `question_en` text NOT NULL, `answer` text, `answer_en` text, `metatags` text, `metatags_en` text, `sorder` tinyint(4) DEFAULT NULL, `visible` tinyint(4) DEFAULT NULL, PRIMARY KEY (`faqid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
Posted On: Monday 31st of December 2012 01:46:13 AM Total Views:  389
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




requirements changed in join...now...how to do it?

Hi I have 2 tables, faq and faq_categories...i have a join that work, and so far, I was a happy camper. But...requirements change, and i have to change the join, but i don't know how to do it Here is the current code that works just fine: Code: SELECT faq.* , faq_categories.categoryname FROM faq JOIN faq_categories ON ( faq.catid = faq_categories.catid) So far, all faq belongs to one category...but from now on, there will be faq which will not belonng to any category....and that complicate things, at least for me. How should I change this code in order to display the faq which does not have catid Here are my tables: Code: CREATE TABLE IF NOT EXISTS `faq_categories` ( `catid` int(11) NOT NULL AUTO_INCREMENT, `parentid` int(11) DEFAULT NULL, `categoryname` varchar(255) NOT NULL, `categoryname_en` varchar(255) DEFAULT NULL, `description` text, `description_en` text, `metatags` text, `metatags_en` text, `sorder` int(11) NOT NULL, `visible` tinyint(4) NOT NULL, `categoryphoto` varchar(255) DEFAULT '', PRIMARY KEY (`catid`), KEY `parentid_fk` (`parentid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=204 ; CREATE TABLE IF NOT EXISTS `faq` ( `faqid` int(11) NOT NULL AUTO_INCREMENT, `catid` int(11) DEFAULT NULL, `question` text NOT NULL, `question_en` text NOT NULL, `answer` text, `answer_en` text, `metatags` text, `metatags_en` text, `sorder` tinyint(4) DEFAULT NULL, `visible` tinyint(4) DEFAULT NULL, PRIMARY KEY (`faqid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
VIEWS ON THIS POST

113

Posted on:

Thursday 25th October 2012
View Replies!

$num_rows

Okay, I haven't done much querying in the past, but this should be easy. Here's my query: Code: $query = "SELECT listing_id, listing_title from Listings left join Listings_images on Listings.listing_id =Listings_images.listing_id order by Listings_images.orderby"; $result = mysql_query($query); $count = mysql_num_rows($result); Here's my error message: Code: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result I don't know if you can tell what's wrong just by looking at my query, so please let me know if you need more info.
VIEWS ON THIS POST

101

Posted on:

Monday 29th October 2012
View Replies!

Garble picture names

I have a user table with different levels of access(privileges). Users can upload photos into server. I would like to store photos the way so that users based on their photos paths would not be able easily figure out other photos paths or names (and view them) that belong to other members. e.g. I sign up as "john_wayne" I don't want to have photo link like view photo 1 I would prefer this view photo 1 How do pros go about that Do they hash photo names and store names as hash vales in database Does anybody know how to accomplish that or knows about some good article. Thank you.
VIEWS ON THIS POST

58

Posted on:

Monday 29th October 2012
View Replies!

Scalability Question: 100 MySQL Databases

I've been working on a new web application that uses PHP/MySQL combination. The site is currently only at 45 members as we just started our closed beta process. Currently, I am only using 1 database for everything. I have a table for users, comments, votes, posts, etc., and the entire site works perfectly. However, I have 100 MySQL databases at my disposal, and the hosting provider puts a 100mb cap on each. My questions is this: We're aiming to have at least 1000 users by the end of February, so is it advisable/possible to change structuring to have 10 databases for users, 10 for comments, 10 for posts, etc. I know I'll have to go back and re-code everything if this is the case, but I don't have a choice really. As a point of reference, we're only using 0.2mb of the available 100mb right now of the db, but I want to fix this now so it's not an issue if we get over 10,000 users. I can clarify if this isn't descriptive enough. I taught myself PHP/MySQL, and do not have a degree of any sort, so my conventions and concepts may be off.
VIEWS ON THIS POST

55

Posted on:

Monday 29th October 2012
View Replies!

SQL statement help

I have a SQL statement created but it's not correct. What I want is to get all the records: from the "tblmailinglist" table where "subscribed" is not equal to the value 'N' but might the "origin" can be equal to "ART", "ASDF", "HUU" etc. My pseudocode is: get records from tblmailinglist where subscribed is "N" and the origin has a value of "ART" or "ASDF" or "HUU" It's the "and" and the "or" together that I'm getting messed up on. The subscribed HAS to equal "N" but the origin can be a variety of differen things. What I have right now: SELECT tblmailinglist.* FROM tblmailinglist WHERE subscribed 'N' OR origin LIKE '%ASDF%' OR origin LIKE '%ART%' OR origin LIKE '%HUU%' OR origin LIKE '%YYU%';
VIEWS ON THIS POST

69

Posted on:

Monday 26th November 2012
View Replies!

MySQL DB: how to check if record already exists

Hi , I've got this MySQL database, full with all kinds of links. The only one who can add links to the database is ... me, this takes to much time. So i came up with the idea to allow my users to add links to my database But who needs double links in their database So I need a way to check if the link already exists in the DB, what would be the best way to do so This what my table looks like: Name | URL | Category ------|-----------------|--------- Link1 | http://URL1.net | Happy sites Link2 | http://URL2.com | Nice sites The URL is the INDEX and the Primary Key for this table. Any suggestions are greatly appreciated.
VIEWS ON THIS POST

68

Posted on:

Monday 26th November 2012
View Replies!

Comparing Dates in SQL

! can anyone please help or had smiliar problems I have an Access database with a field named 'bid_close_date' (data type = 'Date/Time' - General Date: DD/MM/YYYY HH:MM:SS) in a table named tblplayers. I am running the following Query, using NOW() - which I was hopeing would return all the players from tblplayers whose bid_close_date is less than NOW(). SQL = "SELECT * FROM tblPlayers WHERE bid_close_date > # " & NOW &" # ;" HOWEVER! The SQL only seems to be looking at the first part of the Date i.e. DD and not the rest of the date string. i.e. MM/YYYY HH:MM:SS N.B. when I compare dates in VBscript it recognises the dates and the comparisons works. i.e. return the deed result. rs("Bid_close_date") THEN Response.Write("THIS AUCTION CLOSED etc...") End If %> If anyone has any suggestions I would be most grateful. David
VIEWS ON THIS POST

87

Posted on:

Monday 26th November 2012
View Replies!

how to cut short a string?

Originally posted by : kamp (kamph@netease.com)the string saved by MSSQLserver will has some zeros at it's tail. After i get the string from MSSQLserver, how to cut it into exactly what it is (i use c#)
VIEWS ON THIS POST

99

Posted on:

Monday 26th November 2012
View Replies!

query problem

Hi When I give queryselect * from table where bannerid='"&bannerid&"' and visitordate between '1/1/2003' and '1/26/2003'it displays results between the given periodbut when i give queryselect * from table where bannerid='"&bannerid&"' and visitordate between '12/31/2002' and '1/26/2003'it displays no records why is this soPlease help urgentl
VIEWS ON THIS POST

72

Posted on:

Monday 26th November 2012
View Replies!

editing datagrid

Originally posted by : Karin Vanderheide (kvdheide@godutch.com) I seem to be having problems using the OnUpdateCommand and OnCancelCommand in my datagrid: can anyone see where my error isWhen I use the edit button... the boundcolumn turn to textboxes and I receive the response.write message "edit row".However, when i click on the update or cancel button nothing changes... it's like the function in never invoked or run. the response.writes' don't display either.please help! i can't look at it anymore. 'Edit rowpublic sub EditRow(sender as Object, e as DataGridCommandEventArgs) response.write("edit row") CustomerDataGrid.EditItemIndex = e.Item.ItemIndex BindData(sorting,filter)end subpublic sub CancelUpdate(sender as Object, e as DataGridCommandEventArgs) response.write("cancel update") CustomerDataGrid.EditItemIndex = -1 BindData(sorting,filter)end subpublic sub UpdateRow(sender as Object,e as DataGridCommandEventArgs) response.write("update row") Dim editTitle As TextBox = CType(e.Item.Cells(2).Controls(0), TextBox) Dim Item as String = e.Item.Cells(1).Text Dim title As String = editTitle.Text response.write(Item & title) CustomerDataGrid.EditItemIndex= -1 BindData(sorting,filter)end sub
VIEWS ON THIS POST

108

Posted on:

Monday 26th November 2012
View Replies!

Printerfriendly version

Originally posted by : Tore (webmaster@lufs.no)I have a database-driven news-function on my site (haven't written it myself, only moderated it), that allows me to publish news through an admin-page. Now I want to add a function that puts the news into a printerfriendly page. Could someone please help me with this See the page at www.lufs.no/aktuelt/ - and I'll of course e-mail you the source if somebody need it. Please e-mail me at webmaster@lufs.no
VIEWS ON THIS POST

71

Posted on:

Monday 26th November 2012
View Replies!

ADODB.Field-Fehler '80020009'

Originally posted by : Kastlaz (marco.ribi@gmx.ch)What's my problem \t I became every time the same message (ADODB.Field-Fehler '80020009')Please help me Thank you & Greez
VIEWS ON THIS POST

147

Posted on:

Monday 26th November 2012
View Replies!

asp.net and Ms Access

Originally posted by : ajr (arunjustin@rediffmail.com)Is it posible to connect Ms Access with asp.net i tried with Dsn ,it is not exceptingcan any one give me suggestin for itThanking you,ajr
VIEWS ON THIS POST

92

Posted on:

Monday 26th November 2012
View Replies!

showing 25 rows of a table in each page

Originally posted by : yaron (yaron_kh@yahoo.com)helloi'm trying to show 25 rows of a table in a one page and with the NEXT button show the next 25 rows and so on.what is a good way (minimal use of resources)to do thati don't want to leave an open connection to the database
VIEWS ON THIS POST

95

Posted on:

Monday 26th November 2012
View Replies!

Dupplicate problems inserting record in the database

Originally posted by : Ezio (samarelli@altavista.com)Hi to all,I've a problem inserting record in the database across ASP and IIS4/5;The problem is that if I write a script that insert a record in the database eg.Once the script has been executed and the "Thank you" message appears, if I reload the page the system create another record with the same data.I have found a workarround using the Session object of ASP but if an user disable cookie for the session the script would not work properly.Is it possible to avoid dupplicate problem without using the session objectThank you,
VIEWS ON THIS POST

73

Posted on:

Monday 26th November 2012
View Replies!

Searching my Sql Database

Originally posted by : Mike (frankr6805@aol.com)I want to put together a serach for my database but I am having trouble. I know the LIKE commmand but is there some other way to make the search 'smarter' and more comprehensive, would a stored procedure in teh sql server and sql syntax do a better job does anybody know of any examples like this your help is appreciated
VIEWS ON THIS POST

104

Posted on:

Monday 26th November 2012
View Replies!

Sql efficiency

Originally posted by : Richard (litimel@hotmail.com)Is it more efficient to shoot many small, specific sql count queries at a database - or to make one large query and then count what you want with an asp script Are there any general guidelines on where to draw the line on this issueCheersRichard
VIEWS ON THIS POST

50

Posted on:

Monday 26th November 2012
View Replies!

Nulls, zeros, NZ - haaalp

Originally posted by : Nomit (h.babraa@lse.ac.uk)I have SQL that returns the sum of a field. When there are no entries it returns a null. In access you can use NZ (NVL in Oracle) to force a zero to be returned. How do you do this is with ASP I get a message : NZ is considered undefinedCheers
VIEWS ON THIS POST

91

Posted on:

Monday 26th November 2012
View Replies!

Display database fields

Originally posted by : MM (manal.milad@itworx.com)I am new to ASP+ and I need to display fields from a table. I don't want to use a Datagrid nor a DataList. I want to display the fields separatly. What should I useI can already connect to the database and have a dataset.Please help!
VIEWS ON THIS POST

68

Posted on:

Monday 26th November 2012
View Replies!

How can update and add to multiple tables?

Originally posted by : Richard (rsweeting@bahamas.net.bs)How can i update a table and at the same time add to another table.I used the code from this site:http://www.aspfree.com/asp/startpage.aspid=23This works well for me BUT i need to know how i can do this:when the data is sent to update the table how can i code it to also ADD this data to a table.There are 15 records that are updated, i need these 15 records to be ADDED to another table everytime the form is submitted.
VIEWS ON THIS POST

80

Posted on:

Monday 26th November 2012
View Replies!

I would like to use a common used id as a primary key for several pages

Originally posted by : Pingh (pingh@m-ei.com)current;y this is my situationi have one page main.asp that collects user data, and then submits it to a database. I would like i then to transer one field called USERID to another page donations.asp.Donations.asp then collects more user info and using the same UDERID as a primary key in the second database i would like it to record this info, that way i can link the information with the corresponding person.my supivisor told me to submit is as follows:response.redirect "donations.aspUSERID=" &"'" & _Request.Form("USERID") & "'"however this seems to leave is in such a way that USERID is always a string type variable, that wont convert to a INT no matter how hard i try.please helphow can i pass this wihtout using the url bar.
VIEWS ON THIS POST

140

Posted on:

Monday 26th November 2012
View Replies!

displaying text files without using include file

Originally posted by : cem tekin (tekin65@yahoo.com)hi, i'm quite new to asp. i wonder if there's anybody who do have an answer to my problem.i'm trying to insert one of many text files into a page based on the choice of the visitor. the names of the text files are stored in a database and depending on the choice the file name is callaed to the page, but how to open it can't do it with because html operates before asp. is there a way to open a text file using vb script or java script
VIEWS ON THIS POST

80

Posted on:

Monday 26th November 2012
View Replies!

SQL - types of fields

Originally posted by : Jeff (sipek.jeff@post.cz)Can somebody write me a list of all types of fields in sql I know that there are Integer, and Varchar and that's it.Please mail me.Thank you in advance
VIEWS ON THIS POST

76

Posted on:

Monday 26th November 2012
View Replies!

Problem with Update

Originally posted by : ade (adrian@dublin.com)I'm getting a sytax error when i try to update using the following code.Any Ideas 0 ThenstrSQL = strSQL & "orgName='" &_Cstr(Request.Querystring("orgName")) & "'"blnFirstParameter = FalseEnd If strconn = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("../source/database/archive.mdb") set conn = server.createobject("adodb.connection") conn.open strconn Set objCmd = Server.CreateObject("ADODB.Command") Set objCmd.ActiveConnection = conn objCmd.CommandText = strSQL objCmd.CommandType = adCmdText 'Execute the command objCmd.Execute End if%>
VIEWS ON THIS POST

115

Posted on:

Monday 26th November 2012
View Replies!

Export to Excel without ActiveX

Originally posted by : KirHow can I send data from my SQL DB to Excel file format in ie
VIEWS ON THIS POST

134

Posted on:

Monday 26th November 2012
View Replies!

Sql stored procedure

I have to sent out emails to users if they exist in a table I have a propertyid value that I will sent to the stored procedure If the propertyid exists in the table i want to return adminid Then I get that adminid and I want to get the email address of that admin Below is the stored procedure but its giving me an error at @getadminid variable not declared Code: Create PROCEDURE getemail ( @propertyid int, @getadminid int, @getemail varchar(5000) Out ) AS IF EXISTS(SELECT @AdminId FROM Tracking WHERE Propertyid = @propertyid) BEGIN @getadminid=@AdminId Select @Email from Admin where Admin_Id=@getadminid Return @Email End
VIEWS ON THIS POST

114

Posted on:

Tuesday 27th November 2012
View Replies!

Need help with formatting a Query

We had a survey done, with many questions and possible answers the survey was handled by two tables within our Db A table that handled the questions A table that recorded the answers to those questions. The Questions were multi-choice, choose one, and write in your answer for some. Some questions like the multi-choice ones you could chose up to 30 different answers (yes it was that crazy). Each time a person participated in the survey, an entry into the table that recorded the answers was created for each user and their choices. So if User1 answered Question 4 that had 30 possible answers and they chose all 30, the Question 4 had 30 different entries created for that user like this: Code: User1 Question4 I Like peanuts 3/15/2011 5:26:24 PM User1 Question4 I like walnuts 3/15/2011 5:28:14 PM User1 Question4 I like chestnuts 3/15/2011 5:29:12 PM I need to create a query that will combine all the Users answers together and display it in a format like this (Q=question): Code: USER Q1 Q2 Q3 Q4-1 Q4-2 Q4-3 Q5 Beth yes female 5 1 0 1 6 Mark no male 2 0 1 0 7 James no male 1 0 0 0 9 So if Q4 had say 5 multi-choice answers, the top would be Q4-1, Q4-2, Q4-3, Q4-4, Q4-5 And if Q10 had say 10 multi-choice answers, the top would be Q10-1, Q10-2 and so forth. For those columns that have multi-choice, need to detect which columns were picked and signify by a 1=yes 0=no for being chosen by that user (or 0 if the user left it blank for all columns) - currently they hold the text to that answer. I was told that I could use PIVOT in SQL, but I've never done a complext PIVOT on two tables being joined. This is my current QUERY: Code: SELECT survey_response.UserGuid AS [User], survey_question.QuestionId, survey_question.Body AS Question, survey_response.AnswerId, survey_response.AnswerValue AS Answer, survey_response.CreateDate FROM survey_question INNER JOIN survey_response ON survey_question.QuestionId = survey_response.QuestionId WHERE (survey_response.CreateDate BETWEEN CONVERT(DATETIME, '2011-03-13 00:00:00.000', 102) AND CONVERT(DATETIME, '2011-03-13 23:59:59.998', 102)) ORDER BY [User], survey_response.CreateDate How can I fit in a PIVOT call to this query to achieve what I'm looking for
VIEWS ON THIS POST

124

Posted on:

Tuesday 27th November 2012
View Replies!

MySQL Workbench and Primary Keys

I am using MySQL workbench for the first time. I have created an InnoDB relational database. When i connect two tables with a many to one connection it automatically adds a new row in the many table that references the row_id in the other table. When it adds this new row it inserts it as a Primary row. So now i have a table with 2 primary rows. Is this how things should be setup I though every table should only have one primary row Should i set the newly added relational row so that it's not a primary row On a side note. I am using phpMyAdmin through MAMP. I have read some tutorials about connecting MySQL workbench to MAMP's phpMyAdmin. In the tutorials it states that i should configure MySQL workbench to use the file located at /Applications/MAMP/tmp/mysql/mysql.sock but when i go to this location their is no file with this name (http://phpprotip.com/2011/10/using-m...nch-with-mamp/). Anyone know where i can find this file
VIEWS ON THIS POST

69

Posted on:

Friday 28th December 2012
View Replies!

Get mysql query joined outside while loop

I have a multiplayer game site where a user get his list of games like this every 20 second: PHP Code: $sql1=mysql_query("SELECTgp.fk_game_id,gp.player_tiles,gp.infoPop,gp.chatbadge,g.lastdrawFROM".$prefix."_gameplayergp \t\t\t\t\t\t\t\t\t\t\t\tINNERJOIN".$prefix."_gamesgONgp.fk_game_id=g.game_id \t\t\t\t\t\t\t\t\t\tWHEREgp.fk_player_id=$currplayerANDg.invite=0ANDg.deleteby!=$currplayerORDERBYg.lastdrawASC");\t while($row1=mysql_fetch_assoc($sql1)){ \t\t$gameid=$row1['fk_game_id']; \t\t//GETCURRENTGAMEOPPONENTIDANDBOARDDATA// \t\t$sql=mysql_query("SELECTgp.fk_player_id \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t,gp.last_draw_type \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t,gp.player_turn \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t,u.country \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t,u.username \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t,u.profileimg \t\t\t\t\t\t\t\t\t\t\t\tFROM".$prefix."_gameplayergp \t\t\t\t\t\t\t\t\t\t\t\t\t\tINNERJOIN".$prefix."_usersu \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tONgp.fk_player_id=u.id \t\t\t\t\t\t\t\t\t\t\t\tWHEREgp.fk_player_id!=$currplayerANDgp.fk_game_id=$gameid"); \t\t$row=mysql_fetch_assoc($sql); If a player has 30 games it uses alot of resources due to the mysql_query inside the while loop. Every game is stored in 2 db tables in 3 rows. A games table row which holds the game data and 2 gameplayer tabnle rows, one for the current player and one for the opponent. It is the row for the opponent which I have to run a second query for. Is it possible to join this row into the first query so I only run one query every 20 seconds for a player The table schemas is as follows: Table games game_id - int(11) invitetime - timestamp lastdraw - timestamp timetodraw - datetime bag_tiles - text table_tiles - text new_tiles - text invite - int(11) random - int(11) active - int(11) finished - int(11) deleteby - int(11) warn1 - int(11) warn2 - int(11) Table gameplayer id - int(11) fk_game_id - int(11) fk_player_id - int(11) player_tiles - text player_draws - int(11) first_draw - int(11) player_turn - int(11) last_draw_type - int(11) player_passes - int(11) swapped - int(11) player_win - int(11) player_points - int(11) infoPop - int(11) chatbadge - int(11) Table users id - int(11) email - varchar(255) username - varchar(255) password - varchar(50) profileimg - varchar(25) country - int(11) register - date date - timestamp Hope this makes sense and hoping for help :-)
VIEWS ON THIS POST

55

Posted on:

Friday 28th December 2012
View Replies!

Outer apply query

, I'm trying to reach some table variables with the help of outer apply. The query i got so far is this. Code SQL: SELECT T.Firstcol, T.Secondcol, Z.Timmar, B.Timmar, C.Timmar, D.Timmar FROM @tble AS Z, @tble3 AS B, @tble4 AS C, @tble5 AS D OUTER APPLY (SELECT TOP 1 L.Timmar FROM @tble2 AS L INNER JOIN Z (NOLOCK) ON A.Personnummer = T.PNRCol OR B.Personnummer = T.PNRCol) AS tempres Is there any way to make this query work Error message Invalid object name 'Z'.
VIEWS ON THIS POST

85

Posted on:

Friday 28th December 2012
View Replies!

Fetch related rows from tags

I'm using a 2 table tagging system for articles (myisam) and I'm trying to get related articles based on the tags and category_id of the article currently being viewed. What would be the optimal query to achieve this I only seem to be able to find answers for 3 table tag systems. tags: Code: `article_id` int(11) NOT NULL DEFAULT '0', `tag` varchar(99) NOT NULL DEFAULT '', PRIMARY KEY (`article_id`,`tag`), KEY `tag_ix` (`tag`) articles: Code: `article_id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL DEFAULT '', `content` text NOT NULL, `category_id` int(11) NOT NULL, `tags` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`article_id`), KEY `category_id` (`category_id`)
VIEWS ON THIS POST

73

Posted on:

Friday 28th December 2012
View Replies!

Multiple Loops Producing Slow Script Execution

I'm trying to get my head around the best database design from a speed perspective. I've got 3 tables. Table A - Stations Table B - Songs Table C - Station Plays Right now I'm procedurally walking through the steps. PS I PHP Code: //LOOPTHROUGHMYSTATIONS$sql=mysql_query("SELECT*FROMstations")ordie(mysql_error());\t\twhile($row=mysql_fetch_array($sql)){\t\t\t\t\t\t\t\t//LOOPTHROUGHEACHOFTHESONGSTOENSURETHEREISANENTRY\t\t\t\t$sql2=mysql_query("SELECT*FROMsongs")ordie(mysql_error());\t\t\t\twhile($row2=mysql_fetch_array($sql2)){\t\t\t\t\t\t\t\t\t\t\t\t$sql3=mysql_query("SELECT*FROMtracking_entryWHEREsong_id='".mysql_real_escape_string($row2['id'])."'ANDstation_id='".mysql_real_escape_string($row['id'])."'ANDchart_week='".mysql_real_escape_string($currentWeek)."'ANDweek_string='".mysql_real_escape_string($week_string)."'LIMIT1")ordie(mysql_error());\t\t\t\t\t\tif(mysql_num_rows($sql3)!=1){\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t//CREATEANENTRY\t\t\t\t\t\t\t\tmysql_query("INSERTINTOtracking_entry(id,song_id,station_id,chart_week,activity_log,chart_year,week_string,notes,notes_updated)VALUES('','".mysql_real_escape_string($row2['id'])."','".mysql_real_escape_string($row['id'])."','".mysql_real_escape_string($currentWeek)."','".mysql_real_escape_string("AddedRecordOn$date\n")."','".mysql_real_escape_string(date('Y'))."','".mysql_real_escape_string($week_string)."','".mysql_real_escape_string($row3['notes'])."','".mysql_real_escape_string($row3['notes_updated'])."')")ordie(mysql_error());\t\t\t\t\t\t\t\t\t\t\t\t\t\t}\t\t\t\t\t\t\t\t\t\t}\t\t\t\t\t\t} This script is run nightly because it is currently taking about 25 minutes to complete and pretty much bogs down the server. I know there's a better way. One issue is that when I create a new record I have to bring in some data from the last weeks record. This may be a really obscure cry for help but perhaps someone can help. PS: There are about 14,000 records being produced.
VIEWS ON THIS POST

141

Posted on:

Friday 28th December 2012
View Replies!

Flash Builder mobile Application. Php Mysql Error 1048: Column item cannot be null

I created a table Code: CREATE TABLE basket (id INT NOT NULL AUTO_INCREMENT, item VARCHAR(50) NOT NULL, amount VARCHAR(50) NOT NULL); and try to create an insert function: Code: public function add_basket_item() { include '../vo/basketVO.php'; $stmt = mysqli_prepare($this->connection, "INSERT INTO basket (item, amount) VALUES (, )"); $this->throwExceptionOnError(); mysqli_stmt_bind_param($stmt, 'ss',$item->item, $item->amount); $this->throwExceptionOnError(); mysqli_stmt_execute($stmt); $this->throwExceptionOnError(); $autoid = mysqli_stmt_insert_id($stmt); mysqli_stmt_free_result($stmt); mysqli_close($this->connection); return $autoid; } But when I try to test the operation in Flex Builder I get the following error: InvocationTargetException:There was an error while invoking the operation. Check your server settings and try invoking the operation again. Reason: Server error MySQL Error - 1048: Column 'item' cannot be null /../services/DBBase.php on line 26 I have tried various things but just can't get it to work.
VIEWS ON THIS POST

71

Posted on:

Friday 28th December 2012
View Replies!

Next Record by Order

Good day, I've been trying to figure out a way to get the next record by order. Say I have 10 records and an 'order' column/field in the table being 1, 2, 3, 4.....9, 10. So I start at record with 'order' 1 and I need to select the next, it will be 2, etc. Here is my code: http://pastebin.com/eJszP9v3 What happens when I get to 10 How can I automatically loop and get to 1 again It has to be dynamic. Is there some WHERE clause I can put in the query to look for > 10 else == 1 Thank you for your input!
VIEWS ON THIS POST

94

Posted on:

Friday 28th December 2012
View Replies!

error creating user in SQL Server 2008

I am trying to create a new login for my database and keep getting the following error when I try to run the script that contains the connection string: "dorknozzle is not a valid login or you do not have permission micorsoft sql server error 15007" I followed the instructions in my book which say to: -set server authentication to SQL Server and Windows authentication -expand Security-> Logins node and select New Login -select SQL Server authentication and type dorknozzle as username and dorknozzle as password -deselect Enforce password policy -change default database to Dorknozzle Then give full access to dorknozzle by: -select User Mapping from the Select a page pane -check the Dorknozzle table in list -check the db_owner role After I do this, I see that dorknozzle has been added to the list of users under the security node in the Dorknozzle database. Is there something I am missing that would generate this error The connection string I am using is: "Server=Owner-PC\SqlExpress;" & "Database=Dorknozzle;User ID=dorknozzle;Password=dorknozzle" I did notice when I went back to the Security->Logins-> dorknozzle, that the password that was stored looks longer than the one I typed (dorknozzle). Is it supposed to look this way
VIEWS ON THIS POST

51

Posted on:

Friday 28th December 2012
View Replies!

Sending data from Flash to mysql php database

, I am new to php and phpMyadmin. I created my first database and find it really cool. I have a Wordpress website. I am thinking, of using phpMyadmin with Flash as3. I build a login and register page. I think visually it would look great! However, I am somewhat uncertain how you intergrate a as3 fla or swf file into a php database I have been learning php for about 2 days lol. My knowledge is nonexistent. Hope anyone have experience on this and can give me your advice.
VIEWS ON THIS POST

125

Posted on:

Saturday 29th December 2012
View Replies!

A question about indexes

What is the best way to create indexes ... I have a large table with approx 50 columns, 10+ Million Records I have a few queries that I will be running on the table, for example Code MySQL: select col1, col2, col3 .... from mytable inner join othertable on mytable.field=othertable.field where col1=something, col7=something group by col1, col2 for a query like this, would you create a seperate index for each column you are calling in select portion or a seperate index for each column you are calling in the where clause or a sepeate index for each column in the group by or an index on multiple columns together (col1, col2, col3, col7) Any feedback is appritiated.
VIEWS ON THIS POST

127

Posted on:

Saturday 29th December 2012
View Replies!

Multiple INNER JOINs

, I have a catalogue database. The db contains products which are organised into sections, and categories. One product may exists in many sections and many categories. I am creating a search engine for the administrator to search for products. I am a little confused about creating multiple INNER JOINs on one table. I have 2 lookup tables `cataloguesections` and `cataloguecategories`. If I just want to search for sections for example I can run this query: Code: SELECT `catalogue`.`id`, `title` FROM `catalogue` INNER JOIN `cataloguesections` ON `catalogueid` = `catalogue`.`id` INNER JOIN `sections` ON `sectionid` = `sections`.`id` WHERE `sections`.`id` = '1'; But I'm not sure how to search for both sections and categories. Do I need to create a UNION of 2 queries Many
VIEWS ON THIS POST

135

Posted on:

Sunday 30th December 2012
View Replies!

Determine if Article is in results?

On my website, an "Article" can be in different "Sections", and a "Section" can have many "Articles". When a user selects an Article, I want to be sure that the URL has a valid "Section" for the given "Article" and visa-versa, e.g. http://local.debbie/finance/articles/postage-meters-can-save-you-money Below is a query I am working on... Code SQL: $q1 = 'SELECT id, title, description, keywords, heading, sub_heading, published_on, author, body, reference_listing, endnote_listing FROM article AS a RIGHT OUTER JOIN article_section AS a_s ON a.id = a_s.article_id WHERE slug= AND a_s.section_id='; If I want to check that the Article "postage-meters-can-save-you-money" is in the "finance" Section (along with possibly other Sections), how do I need to modify the above query\t Do I want an INNER JOIN looking for an exact match, or a RIGHT OUTER JOIN just making sure the "Section" is in set of all Sections related to the Article Hope that makes sense!
VIEWS ON THIS POST

161

Posted on:

Sunday 30th December 2012
View Replies!

Multiple UPDATES vs. One UPDATE??

My website has a Private Message center which works in a manner very similar to how Yahoo Mail works. When a User views his/her Inbox, next to each Message is a check-box. By checking certain Messages in the Inbox, then choosing an "action" (e.g. Mark as Unread) and then "Go", the script will update the Messages accordingly. Now on to my question... So currently, when the user submits the Form, I store all Messages that he/she checked in an Array. From there, I LOOP through the Array values, and run a separate UPDATE query for each Message that was chosen. Would it be better to eliminate the ForEach Loop, and somehow run a SINGLE UPDATE query\t From my research online, some people say one UPDATE is better, and yet others say multiple UPDATES are better! What are the Pros and Cons of each approach Also, if eliminating my Loop and having one query is the way to go, how exactly would I take all of the Messages in my Array and stick them in One Query\t
VIEWS ON THIS POST

75

Posted on:

Sunday 30th December 2012
View Replies!

Display all derived fields in a query

I have a very big and complicated query, but the main idea of the query is: Code MySQL: SELECT t.field1, SUM(t.total) AS total FROM ( ( SELECT CASE WHEN field IN ( [list 1] ) THEN "ABC" WHEN field IN ( [list 2] ) THEN "DEF" ELSE "GHI" END AS field1, total as total FROM table1 LEFT JOIN table2 ... WHERE (bunch of where clauses) ) UNION ALL ( SELECT CASE WHEN field IN ( [list 1] ) THEN "ABC" WHEN field IN ( [list 2] ) THEN "DEF" ELSE "GHI" END AS field1, total as total FROM another_table LEFT JOIN yet_another_table ... WHERE (bunch of where clauses) ) ) AS t GROUP BY t.field1 ORDER BY FIELD(t.field1, "ABC", "DEF", "GHI") I'm UNION'ing the results from two queries, then grouping by field1 and summing up the total field for each field1. My question is - if one of the field1 values (say DEF) is not found, the query results do not include DEF. What I'd like is to always show each value of field1 and display 0 for the total if it wasn't found. How would I do this Current Output: ABC 1930 GHI 765 What I'd like to get: ABC 1930 DEF 0 GHI 765
VIEWS ON THIS POST

146

Posted on:

Sunday 30th December 2012
View Replies!

COUNT Causing Problems.

Hi all What I have below works good and returns all the reviews depending on the venue_id selected. Code SQL: SELECT comments, DATE_FORMAT(review_date, '%M %D %Y') AS subdir, publisher, rating, FROM tbl_reviews WHERE venue_id='"... Example return comments: some comments about the venue date: 12th dec 12 rating: 4 comments: some comments about the venue date: 13th dec 12 rating: 2 and so on... But now I need to COUNT the number of reviews for that particular venue_id. Code SQL: SELECT comments, DATE_FORMAT(review_date, '%M %D %Y') AS subdir, publisher, rating, COUNT(venue_id) AS VenueReviewTotal FROM tbl_reviews WHERE venue_id='"... Example return comments: some comments about the venue date: 12th dec 12 rating: 4 and stops, only shows one review even if there are 15 The review count is correct VenueReviewTotal 15 What is happening, why does this happen when I introduce the COUNT function
VIEWS ON THIS POST

383

Posted on:

Monday 31st December 2012
View Replies!

Order by count

I have a query that orders results by the count of another table: "SELECT table1.*, COUNT(table2.id) AS rev_count FROM table1 LEFT JOIN table2 ON table1.name = table2.name WHERE table1.active = 1 AND table1.city='$city' GROUP BY table1.name ORDER BY rev_count DESC"; How do you add a condition (ie WHERE active=1) to just the "COUNT(table2.id) AS rev_count" part of the query
VIEWS ON THIS POST

415

Posted on:

Monday 31st December 2012
View Replies!

Update TABLE from a Related Table with Multiple Entries (only wanting the latest val)

The example below is not real, but if I can solve this, I know I can solve the bigger problem I have! Using MYSQL, I have two tables. Farm and Animals. Animals can have several items connected to farm with the "farmid" serving as a related key. FARM id farmname latestanimal ANIMALS animalid farmid animalname dateadded What I need to do is run an update query, that automatically populates "farm.latestestanimal" with the "animalname" from the latest entry of Animals that is connected to the farm. I know this sample below is wrong, but it might show what I am trying to do: "UPDATE farm SET farm.latestanimal = animals.animalname WHERE animal.farmid = farm.id ORDER BY animal.dateadded DESC LIMIT 1" Can someone point me in the right direction to making this work
VIEWS ON THIS POST

437

Posted on:

Monday 31st December 2012
View Replies!

Change column to autonumber (MSSQL)

After six years of constant nagging from me, my office has finally implemented a copy of MSSQL to replace Access (Woohoo!), so I am now going through and making changes to my queries, which is going quite well. The one problem I noticed was that when the server people imported my databases, they did not always preserve the column properties properly. I'm getting "cannot insert null value" errors for columns that should be autonumber and primary key. Is there: 1) A query I can issue to check the column properties, and 2) A query I can issue to change an existing column to autonumber and make it a primary key
VIEWS ON THIS POST

399

Posted on:

Monday 31st December 2012
View Replies!

Using DISTINCT and COALESCE together?

Below is a query which gives me the Usernames of the Last-10 Visitors to a Member's Profile... Code SQL: // FORMER QUERY $q8 = "SELECT COALESCE(m.username,'Non-Member') AS username FROM visitor_log AS v LEFT OUTER JOIN member AS m ON m.id = v.visitor_id WHERE v.member_viewed_id= ORDER BY v.created_on DESC LIMIT 10"; It produces output like this... The last 10 Visitors on this page were... username4 username4 JohnDoe JohnDoe Non-Member Non-Member Non-Member Non-Member Non-Member Non-Member Is there a way to use DISTINCT so that I would instead get a list like this... The last 10 Visitors on this page were... username4 JohnDoe Non-Member
VIEWS ON THIS POST

447

Posted on:

Monday 31st December 2012
View Replies!

string comparison question

been away for a while and am obviously rusty. Please assume 'today' is Saturday. I am trying to output the days on which a product is available either side of today. So it could be Thursday and Tuesday. I have tried using the following query to return the max(day_of_week) before today - which is in fact Friday but, it returns 'Monday'. here's the table Code MySQL: CREATE TABLE `product_times` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `product_on_sale_id` bigint(20) NOT NULL, `day_of_week` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL, `time_of_day` time DEFAULT NULL, `max_spaces` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL, `spaces_sold` int(11) DEFAULT NULL, `remaining_spaces` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL, `on_off` char(3) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `product_on_sale_id` (`product_on_sale_id`,`day_of_week`,`time_of_day`), CONSTRAINT `productTimes_productsOnSale_fk` FOREIGN KEY (`product_on_sale_id`) REFERENCES `products_on_sale` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1985 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci and the query Code MySQL: select max(day_of_week) from product_times as pt inner join products_on_sale as pos on pos.id = pt.product_on_sale_id and pos.product_range_id = 199 where pt.day_of_week
VIEWS ON THIS POST

437

Posted on:

Monday 31st December 2012
View Replies!

two unique indexes

If there are 10 columns in the table then can we set "index type" = "unique" for 2 or more columns Or is it limited to only 1 column vineet
VIEWS ON THIS POST

557

Posted on:

Monday 31st December 2012
View Replies!