Help with structure - primary key - inner join (i think)?

version 4.1 / using php admin

Hi all

I basically have a no. tables storing photographs and need to understand how I can get this structure right I think a inner join is the right way What I want is to be able to select the 'date' and 'photo_set' and display all the photos

What I have so far:

- date / date, primary key
- title / varchar 90

- photo_id / primary key
- photo_set

- date
- photo_set

Posted On: Thursday 25th of October 2012 11:07:38 PM Total Views:  455
View Complete with Replies

Related Messages:

Trigger for Duplicate rows (Please help)   (198 Views)
Im creating an employee database for TAFE. I have an employee table with employee_id and place fields. The trigger is activated by the Before Insert event. When there's a duplication of employee_id and place the trigger generates a message. For example if a new inserted row has employee_id 20 place 6, and there is a row with employee_id 20 and place 6 already existed in the Employee table, the trigger generates a message: Duplicate Employee_id 20 Place 6. I don't want a unique constraint. The code is below: Code: CREATE TRIGGER employee_tg ON employee BEFORE INSERT ON employee FOR EACH ROW BEGIN IF NOT EXISTS (SELECT 1 FROM employee e INNER JOIN INSERTED i ON i.employee_id=e.employee_id AND INSERT INTO employee(employee_id,place) SELECT employee_id,place FROM INSERTED ELSE RAISEERROR ('Duplicate employee_id 20 Place 6) END IF; END DELIMITER; Im having syntax problem in MYSQL. What could be wrong
Changing value's, query help.   (193 Views)
I have a table i need to edit i want to replace all values of 3 to 1 within a column. eg. change blah to 3 where blah = 1 just not 100% sure on the right syntax. hopefully someone can point me in the right direction.
Need help with this query...   (176 Views)
I need to know if it's possible to have a query that will select products from the database that belong to a certain store...but if there is no product like it that belongs to that store then use the default store. Here would be the table layout: TYPE PRODUCT STORE 1 CoolProduct Walmart 1 CoolProduct default 2 CoolProduct2 default ... So if I wanted to get all the products with 'Type' of '2' that were from Walmart but if Walmart isn't found then use the 'store' called 'default' how would I do that (This part is actually used within a JOIN statement so I can't just LIMIT 1 and do an ORDER BY deal because I'm actually pulling a number of different 'types' at once...just need to use the default if it can't find a 'store' that matches.)
if else stament error please help....   (194 Views)
hi im still not very well familiar with using if else statements in Mysql can anyone help me i made a query like this Code SQL: SELECT degree_code FROM courses_code this query selects all the degree_code from the courses_code table now what i need to do is to filter it Code SQL: IF degree_code = XXXX THEN degree_code= " " END IF how can i incorporate that code from the first query i tried something like this but it didn't work Code SQL: SELECT degree_code IF degree_code=XXXX THEN SET degree_code = " " #or just put a NULL VALUE END IF FROM courses_code
help to make query work on version 5.0.51a   (254 Views)
can someone help me make this query work on version 5 it was working fine on version 4, but now it gives me the message #1054 - Unknown column 'massage_charge.patient_id' in 'on clause' SELECT , , massage_schedule.finish , , massage_charge.service_date , massage_charge.amount , massage_charge.tip , massage_patients.first , massage_patients.last , massage_patients.ES , massage_patients.massages , massage_patients.date_created , massage_schedule.finish , CASE WHEN DATE_ADD(massage_therapists.hire, INTERVAL 90 DAY) > CURDATE() THEN CASE WHEN massage_schedule.finish = 60 THEN 18 WHEN massage_schedule.finish = 90 THEN 27 WHEN massage_schedule.finish = 120 THEN 36 END WHEN DATE_ADD(massage_therapists.hire, INTERVAL 180 DAY) > CURDATE() THEN CASE WHEN massage_schedule.finish = 60 THEN 19 WHEN massage_schedule.finish = 90 THEN 28 WHEN massage_schedule.finish = 120 THEN 38 END WHEN DATE_ADD(massage_therapists.hire, INTERVAL 6 MONTH) = DATE_SUB('2008-09-03',INTERVAL 6 DAY) AND massage_charge.service_date
mysql query help required   (423 Views)
In one table there are some records for a field: option=com_content&id=1&task=view option=com_content&id=10&task=view option=com_content&id=91&task=view and so on. How to write a query to get the records only when id=1
MS Access Querie or VBA Code help   (184 Views)
I have an access database table which includes the following 2 fields (vendorID and Frequency) the Frequency column contains the following records (weekly, monthly, quarterly, half yearly and yearly) what i want to do is display the vendor records in a query which are due based on there frequency critea matching the current date. for example weekly in frequency will get displayed every week monthly in frequency will get displayed every month quarterly in frequency will get displayed every 4 months i want this because i have a list of reports that get sent to me based on the frequency criteria, and it would help if i can have a querie that displays the reports i'm suppose to recieve and if not i can chase up on them. i've heard that dateDiff() could be used for this
Creating DB help   (236 Views)
Hi , I need some help for my databases. My know knowledge with SQL is not big. I want to make a database for job candidates. I've made the html form and now im working on the db. So i created a db and i need to creat one table but im not sure now to do it right. The columbs are: 0. ID 1.Name 2.Surname 3.Family Name 4. Personal ID 5. Sex 6. Job Type 7. Email 8. Phone primary key ( ID, Personal ID ). Also the ID and PerID should be unique. I've made a simple sql code but it's not right. pls have a look and tell what to do. The second thing is how the users will fill those tables with info. Code: CREATE TABLE users ( userid int unsigned not null auto_increment, name int char(20) not null, surname int char(20) not null, familyname int char(20) not null, personalid int unsigned char (10) not null, sex int char(10) not null, jobtype int char (20) not null, email , phone , );
Need help writing a query to join multiple fields   (226 Views)
I am trying to write a query for a single table where each record contains five part number fields. I've never done this type of query before and could use some help. Here is the format of the table: Code: Table Name: MyTable Fields: widget_1_partnum widget_2_partnum widget_3_partnum widget_4_partnum widget_5_partnum widget_1_description widget_2_description ... I want a query that will merge and return all distinct widget part numbers in a single result set field called partnum, independent of whether the part numbers are related to widget_a, widget_b, etc. Basically I want to lump all the widget part numbers (from all five fields) into a single group and then have it return distinct part numbers from that group in a single result variable that I can index through in PHP (let's call it "partnum"). Anyone know how to do this Here is the code I'm trying to generate with this query: Code: $result=mysql_query(""); $num_rows=mysql_num_rows($result); for ($i=0;$i
Replace help?   (300 Views)
Hi Guys, I've googled this but can't find the right answer. Say I want to replace all instances of the character & in my database for 1,000's of records. I thought it would be something like so: Code: update table set headline = replace('&','&'); Can anyone help
REPLACE help in a query   (197 Views)
Hi , I want to run a query in phpmyadmin: Code: SELECT story_id, site_id, area_covered FROM `cms_stories` WHERE area_covered = 'ascot'; Which then brings out all my data like so: But then I want to take all of these query results and replace each site_id column with 51 INSTEAD of 52. Is there a way I can do this in a query rather than editing manually
SQL query... please help   (190 Views)
I am trying to generate an sql query but am having problems getting the result in a single query. Could someone please help point me in the right direction. table is as follows: PHP Code: id url timer value 1 localhost/index.php connect 1.0 2 localhost/index.php download 1.3 3 localhost/test.php connect 0.9 4 localhost/test2.php connect 1.1 5 localhost/test2.php download 1.1 What I'm trying to do is generate a list of urls that are the quickest to connect AND download. therefore the results should not include localhost/test.php (only connected). from the data above, the generated list should be something like 1) localhost/test2.php -> 2.2 2) localhost/index.php -> 2.3 so far I've got it working with two queries PHP Code: $query="SELECTCOUNT(url)ask,urlfromtableGROUPBYurl";$result=mysql_query($query);while($row=mysql_fetch_assoc($result)){ if($row['k']==2) { $urls[]=$row['url']; }}$urls='\''.implode('\',\'',$urls).'\'';$query="SELECTurl,SUM(value)asmaxfromtableWHEREurlIN($urls)GROUPBYurlORDERBYmaxASCLIMIT10";$result=mysql_query($query);while($row=mysql_fetch_assoc($result)){ show($row); }functionshow($array){ echo''; print_r($array); echo''; } Is it possible to select the data with a single query
complex query help   (350 Views)
, I'm completely stumped. PHP Code: Table1locationid|location|lat|longTable2routeid|routename|deplocation|arrlocation So that's an abstract of my table structure. One table holds the locations (the airports), and another table (could be routes, reports), etc. locationdep and locationarr both reference back to table 1. Here's my problem... I need lat/long for both of them included into the query, so in the end: PHP Code: routeid|routename|deplocation|deplat|deplong|arrlocation|arrlat|arrlong I figure pulling them from MySQL at once beats doing 3 queries to pull one complete record - when there would be upwards of 20/30 records being pulled per page. I've tried UNION, JOIN, etc, but I can't really wrap my head around this. It's almost 2 am... need some sleep. I'm not even sure what this type of query is called, so it's tough to search for a solution.
3 table join in a php function help?   (223 Views)
Hi , I wrote this function a while ago and it works fine but now I want to make it better by moving the query out of the while loop because I think this is causing alot of mysql overhead. If you can give me some idea as to how I would do this or even just some suggestions that would be great. Also any ideas on how to better write the code in general is greatly appreciated. Well here is the code. Code PHP: function GetGalleries ($user_id) { //this gets the categories that belong to the current user $get_cats = "SELECT id, user_id, cat_name, cat_desc from cats where user_id = '$user_id'"; $cats_res = mysql_query($get_cats)or die(mysql_error()); $num_of_cats = mysql_num_rows($cats_res); while ( $category = mysql_fetch_array($cats_res) ) { $cats_id = $category['id']; $cats_name = stripslashes($category['cat_name']); //then this gets the category name and the first picture to use as the thumnail link on the page $get_cat = "SELECT cat_id, photo_id from cat_photo JOIN photos on cat_photo.photo_id = WHERE cat_id = ".$cats_id." ORDER by limit 1"; $cat_res = mysql_query($get_cat)or die(mysql_error()); $cat = mysql_fetch_array($cat_res); $pid = $cat['photo_id']; $cats[] = array( 'cat_id' => $cats_id, 'cat_name' => $cats_name, 'pid' => $pid); } //then it returns an array of the galleries return($cats); }
help with a query   (309 Views)
hello, I'm going crazy trying to figure this out: I have 2 tables named videos and channelsForeign key is Channel_ID where I store the id of the channel the video belongs toRelevant fields for table videos are: Video_ID, Video_PostedOn, Channel_IDRelevant fields for table channels are: Channel_ID, Channel_Name I need to get the 3 most recent videos for each Channel_ID currently present in the videos table, ordered by Channel_Name ASC. I would really appreciate it if someone could help me out with this. maybe r937
Need help with complex query   (207 Views)
i've got 2 tables TABLE 1 contain a list of color with field color_id, title, description TABLE 2 contain connection between different color ex: colorA | colorB 1 - - - - 2 1 - - - - 8 1 - - - - 9 2 - - - - 3 8 - - - - 6 8 - - - - 7 what I want is to be able to list all ColorB for a specific ColorA, but ordered by the most connection from all colorB ..... so if I list connection for colorA #1 it should give me 8 2 9 as 8 have 2 colorB, 2 have 1colorA and 9 have no colorB at all! Is there a way to do this in 1 query
Error trying to import to please   (497 Views)
I exported a small database to my computer Desktop. A .sql file. I asked my new web host to help me import it my new web mysql. I uploaded it and they attempted it directly to mysql(no phpmyadmin) and they replied "the importing of the backuped file to your database gives errors, and overwrites existing settings" and then provided the foloowing information below. Can someone help me tell me what this means and explain what went wrong and how this can be remedied Thank you. Query: CREATE TABLE `accounts` ( `account_id` bigint(20) NOT NULL auto_increment, `user_name` varchar(50) NOT NULL, `first_name` varchar(100) NOT NULL default '', `middle_initials` varchar(10) NOT NULL default '', `last_name` varchar(100) NOT NULL default '', `password` varchar(100) NOT NULL, `security_question` varchar(250) NOT NULL default '', `security_question_answer` varchar(250) NOT NULL default '', `email` varchar(200) NOT NULL, `phone` varchar(100) NOT NULL default '', `mobile_phone_number` varchar(100) NOT NULL default '', `account_type_id` int(11) NOT NULL default '1', `address` varchar(255) NOT NULL default '', `address_second` varchar(255) NOT NULL default '', `state` varchar(150) NOT NULL default '', `city` varchar(150) NOT NULL default '', `zip_code` varchar(150) NOT NULL default '', `country_id` int(11) NOT NULL default '0', `country_name` varchar(100) NOT NULL default '', `status_id` int(11) NOT NULL default '1', `status_title` varchar(50) NOT NULL, `date_creation` date NOT NULL, `date_last_login` date NOT NULL, `user_agreement` varchar(20) NOT NULL, `last_ip_address` varchar(20) NOT NULL, `activation_code` varchar(100) NOT NULL default '', `date_last_payment` date default NULL, `terms` varchar(3) default 'yes', `login_count` int(11) NOT NULL default '0', PRIMARY KEY (`account_id`), UNIQUE KEY `ix_accounts_email` (`email`), UNIQUE KEY `ix_accounts_user_name` (`user_name`), KEY `ix_accounts_first_name` (`first_name`), KEY `ix_accounts_family_name` (`last_name`), KEY `ix_accounts_country_id` (`country_id`), KEY `ix_accounts_status_id` (`status_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 Error occured at:2007-10-01 19:26:42 Line no.:59 Error Code: 1050 - Table 'accounts' already exists
TSQl help: Testing a stored proc with output params in SQL Server Managment Studio   (292 Views)
I have an SP like this (edited for brevity): set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[TESTING_SP] @Username MediumText, @Password MediumText, @UserKey int OUTPUT, @RoleKey int OUTPUT, @UserGroupKey int OUTPUT, AS BEGIN SELECT @UserKey = UserKey FROM UserProfile WHERE Username = @UserName AND [Password] = @Password END I want to execute this sp in Managment Studio (MS) and see what is being returned but I'm getting this error: Msg 201, Level 16, State 4, Procedure TESTING_SP, Line 0 Procedure 'TESTING_SP' expects parameter '@UserKey', which was not supplied. How do I set up the output parameters and then select the values in MS for testing purposes
Complex SELECT statement help - is it possible?   (165 Views)
Ok..I have a many to many relationship and I want to look at one table, one row at a time, and print all values for each row. However, for each row, if there a relationship has been found in another table, I want to print that tables relationship data also....briefly: scan table1, 1 row at a time; if table2.valueid = table1.value { print table1.allvalues and table2.allvalues } else print table1.allvalues My question is this: Is this possible through solely using a SELECT statement
Need help understanding error message   (162 Views)
I'm not terribly experienced with databases. Can someone tell me what's wrong with this query I've included the error message below - I'm not sure what it's telling me or how I fix it. CREATE TABLE saClient( clientID INTEGER UNSIGNED NOT NULL , firstName VARCHAR NOT NULL AUTO_INCREMENT , middleName VARCHAR NULL , lastName VARCHAR NULL , address1 VARCHAR NULL , address2 VARCHAR NULL , city VARCHAR NULL , state VARCHAR NULL , zip VARCHAR NULL , birthday VARCHAR NULL , ssn VARCHAR NULL , fax VARCHAR NULL , email1 VARCHAR NULL , email2 VARCHAR NULL , phoneCell VARCHAR NULL , phoneHome VARCHAR NULL , phoneWork VARCHAR NULL , phoneOther VARCHAR NULL , emergContactName VARCHAR NULL , emergContactPhone1 VARCHAR NULL , emergContactPhone2 VARCHAR NULL , emergContactRelationship VARCHAR NULL , sex TINYINT UNSIGNED NULL , notes TEXT NULL , STATUS TINYINT UNSIGNED NULL , PRIMARY KEY ( clientID ) ) MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL AUTO_INCREMENT, middleName VARCHAR NULL, lastName VARCHAR NULL, ' at line 3