corsasport.co.uk
 

Corsa Sport » Message Board » Off Day » Geek Day » MYSQL Problem(s)


New Topic

New Poll
  Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.


Author MYSQL Problem(s)
liamC
Member

Registered: 28th Feb 04
User status: Offline
13th Jan 07 at 19:22   View User's Profile U2U Member Reply With Quote

Made a mysql database with tables etc which is all fine and up and running.

Made an HTML/PHP page to input data into the database and I inputted one row into the table with about 8 fields with no bother whatsoever...yay! ....BUT now it won't let me add anything else to the database other than one row! I can delete the one row of data I have in and add another one with no bother...but I can't add anymore than one!

Any ideas? Maybe just a quick option I haven't enabled/disabled?
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
13th Jan 07 at 19:29   View Garage View User's Profile U2U Member Reply With Quote

Do you have an auto_increment primary key? Show me the output to:

SHOW FIELDS FROM tablename;
liamC
Member

Registered: 28th Feb 04
User status: Offline
13th Jan 07 at 19:32   View User's Profile U2U Member Reply With Quote

Yeah I have an auto incremement key (called fldID) which I want it to give it an automatic ID to each row but I can't get it generating its own ID.

I take it that this is the problem?

[Edited on 13-01-2007 by liamC]
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
13th Jan 07 at 19:38   View Garage View User's Profile U2U Member Reply With Quote

Run that command.
liamC
Member

Registered: 28th Feb 04
User status: Offline
13th Jan 07 at 19:42   View User's Profile U2U Member Reply With Quote

Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
13th Jan 07 at 19:49   View Garage View User's Profile U2U Member Reply With Quote

ALTER TABLE tblGMD CHANGE fldID fldID INT(15) UNSIGNED NOT NULL AUTO_INCREMENT;
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
13th Jan 07 at 19:51   View Garage View User's Profile U2U Member Reply With Quote

The Extra colum needs auto_increment flag, just hadn't been set up right. Also when you're inserting, use something like

INSERT INTO tblGMD VALUES ('', '47', '23'.... etc. so the auto_increment puts the value in the first field without you supplying anything.
liamC
Member

Registered: 28th Feb 04
User status: Offline
13th Jan 07 at 19:56   View User's Profile U2U Member Reply With Quote

You're a star mate! Working spot on, thanks alot.

One more thing which I dunno if you can help with or whether it is easy.

Basically the database is for a car company.

This section is for the staff to add new vehicles to the database for sale.

Ideally I need 2 drop down boxes for the make and model(I have 2 other tables, one for make, and one for model) where the user can choose the make and model from the menu.
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
13th Jan 07 at 20:00   View Garage View User's Profile U2U Member Reply With Quote

So you want to fill the dropdown boxes with stuff from the database?

Easy enough, obviously you'll have to play about with the table and field names.

echo "<select name=\"make\"><option value=\"0\">No make specified</option>";
$makequery = $db->query("SELECT * FROM makestable ORDER BY name ASC");
while($make = $db->fetch_array($makequery)) {
echo " <option value=\"$make[id]\">$make[name]</option>";
}
echo "</select>";
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
13th Jan 07 at 20:02   View Garage View User's Profile U2U Member Reply With Quote

They will be two separate dropdowns though. If you want to have so you select Vauxhall then only get Vauxhall cars on the other list you'll either need to repopulate the list using AJAX (nifty but difficult), or reload the page (clunky and slow) or put the model on another page further through the application.

A problem though - model table must be quite big? Not a problem if you can WHERE on the make, but you can't do this unless the user has selected one. Need to think about how that is going to behave first before you can program it.

[Edited on 13-01-2007 by Ian]
liamC
Member

Registered: 28th Feb 04
User status: Offline
13th Jan 07 at 20:04   View User's Profile U2U Member Reply With Quote

Many thanks, I'll give it a go.

Does that code go into the on the drop down box?

Basically I have an *.HTML page with all the fields to input data, then when I click 'add vehicle' it then takes me to the PHP page with all the required info to insert the data into the table.

Will I have to make the *.HTML page into a PHP one in this case?
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
13th Jan 07 at 20:06   View Garage View User's Profile U2U Member Reply With Quote

Yeah the pages that your dropdowns appear on will have to be a PHP page so you can run the database queries on it.
liamC
Member

Registered: 28th Feb 04
User status: Offline
13th Jan 07 at 20:06   View User's Profile U2U Member Reply With Quote

Well basically I have a make table and a model table and I am busy populating the model table bevause as you say it's massive!

I have an ID column for each make in the make table; 1 = Alfa Romeo, 2 = Audi etc, then I also have an ID column in the model table 1 = 146, 2 = 156 if you catch my drift...
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
13th Jan 07 at 20:57   View Garage View User's Profile U2U Member Reply With Quote

Yeah, make and model are normalised as they should be.

Your biggest problem is prohibiting the user from selecting a Ford Polo.
liamC
Member

Registered: 28th Feb 04
User status: Offline
13th Jan 07 at 21:01   View User's Profile U2U Member Reply With Quote

Having trouble getting this drop down working. The first echo and the "; dont get recognised within the code by Dreamweaver. I presume that's Dreamweaver being it's normal self?

Also, how would I connect to the database so it gives me the list of results from the drop down?

Cheers for your help BTW.

[Edited on 13-01-2007 by liamC]
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
13th Jan 07 at 21:14   View Garage View User's Profile U2U Member Reply With Quote

Thats PHP not HTML. Try it in code view.

Personally I would copy a page you already have working that has selects on and paste that code in.
liamC
Member

Registered: 28th Feb 04
User status: Offline
13th Jan 07 at 21:16   View User's Profile U2U Member Reply With Quote

Yeah I changed to PHP mate but the 2 problems were as above.

I'll keep trying. Cheers
liamC
Member

Registered: 28th Feb 04
User status: Offline
13th Jan 07 at 21:34   View User's Profile U2U Member Reply With Quote

quote:
Originally posted by Ian

Your biggest problem is prohibiting the user from selecting a Ford Polo.


How would you suggest getting around this mate?

Obviously I want it so that when you choose a Ford it only lists Ford models etc, just like the Auto Trader site...
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
13th Jan 07 at 22:05   View Garage View User's Profile U2U Member Reply With Quote

Have a look how they do it. AJAX is the best approach but also the most difficult.
liamC
Member

Registered: 28th Feb 04
User status: Offline
13th Jan 07 at 22:20   View User's Profile U2U Member Reply With Quote

quote:
Originally posted by liamC
Can't get this drop down working to save my life

My data is coming from the table tblMAKE
The data I want listed is in the field fldMAKE

Would this be right?

code:
echo "<select name=\"Make\"><option value=\"0\">No make specified</option>";
$makequery = $db->query("SELECT fldMAKE FROM tblMAKE ORDER BY name ASC");
while($fldMAKE = $db->fetch_array($makequery)) {
echo " <option value=\"$fldMAKE[id]\">$fldMAKE[name]</option>";
}
echo "</select>";
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
13th Jan 07 at 22:58   View Garage View User's Profile U2U Member Reply With Quote

It needs two fields - the primary key integer which goes inside the value attribute of the option tag, and the name which gets displayed on the list.

The select should be more like

SELECT fldID, fldMAKE FROM tblMAKE ORDER BY fldMAKE asc
James
Member

Registered: 1st Jun 02
Location: Surrey
User status: Offline
14th Jan 07 at 02:48   View User's Profile U2U Member Reply With Quote

quote:
Originally posted by Ian
Have a look how they do it. AJAX is the best approach but also the most difficult.

Dunno about PHP but in ASP.Net I would only bind data to the second dropdown (model), when the make drop down list changes, that way you can pass the make into the parameter and bind the model dropdown list to Select * From TABLE Where Make = Value passed in from first dropdown.
liamC
Member

Registered: 28th Feb 04
User status: Offline
14th Jan 07 at 12:18   View User's Profile U2U Member Reply With Quote

Had a rethink on these drop down boxes and I think I'm going to do it a different way...

The company is only local so the chances of them having every single make and model car in stock is very very minimal, so ideally I need a list of makes taken from the make table that the company only has in stock, and from there the second drop down will list the models of each make that there is in stock.

Think that will be better...it's just implementing it all which is the fun!
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
14th Jan 07 at 14:21   View Garage View User's Profile U2U Member Reply With Quote

The ASP.net bindings are just a simple way of doing it for you, same theory as you still need to get the data back to the client, just .net will automate the development process. PHP you are pretty much on your own.

 
New Topic

New Poll

  Related Threads Author Forum Replies Views Last Post
Linking .jsp pages to a MySQL database chris_lee100 Geek Day 7 820
1st Feb 04 at 14:23
by Ian
 
forum hacking - SQL's Drew Geek Day 15 1046
9th Sep 04 at 23:50
by Ian
 
Server Help Jodi_the_g Geek Day 7 838
19th Jan 06 at 15:18
by Jodi_the_g
 
My SQL-Nearly there! Tom J Geek Day 31 1737
25th Dec 06 at 23:32
by Ian
 

Corsa Sport » Message Board » Off Day » Geek Day » MYSQL Problem(s) 28 database queries in 0.0149281 seconds