Combobox

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: Combobox

Post by ursl40 »

So, there's no other way to solve this - that the car could be inserted with no user selected from combobox (they are connected through foreign key so, users.id = cars.user_id)?
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Combobox

Post by McInfo »

In the database, allow the user id in the cars table to be null. Modify the foreign key constraint in the cars table that references the id in the users table to "ON DELETE SET NULL" and "ON UPDATE CASCADE". Set the user id in the cars table to NULL when no user is associated.

(You don't have a "combo" box.)
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: Combobox

Post by ursl40 »

I've done what You've suggested, but no success.

1. In the database, allow the user id in the cars table to be null. OK

2. Modify the foreign key constraint in the cars table that references the id in the users table to "ON DELETE SET NULL" and "ON UPDATE CASCADE". OK

3. Set the user id in the cars table to NULL when no user is associated. Isn't that the same as first?
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Combobox

Post by McInfo »

The first implies a change to the table structure. In the third, by "set the user id" I mean use NULL values in UPDATE and INSERT queries.
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: Combobox

Post by ursl40 »

It's still not working. I can't find INSERT query, that must be NULL, only DELETE and UPDATE. It writes me:
'Could not enter data: Cannot add or update a child row: a foreign key constraint fails (`mydb`.`cars`, CONSTRAINT `cars_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE SET NULL)'
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Combobox

Post by McInfo »

Export the database structure in SQL format and post it so we can see the CREATE TABLE and ALTER TABLE queries. Also post the query that fails.
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: Combobox

Post by ursl40 »

DB structure:
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `cars` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`surname` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

ALTER TABLE `cars`
ADD CONSTRAINT `cars_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE SET NULL;

Query:
$sql = "INSERT INTO cars ".
"(name,user_id) ".
"VALUES ".
"('$name','$id')";
"WHERE user.id = cars.user_id";
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Combobox

Post by McInfo »

A table is created with the name "users", but the constraint references "user".

Code: Select all

FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
#                                      ^
There are two PHP statements here.

Code: Select all

$sql = "INSERT INTO cars ".
"(name,user_id) ".
"VALUES ".
"('$name','$id')";
//               ^ semicolon ends statement
"WHERE user.id = cars.user_id";
//        ^ is it user or users?
The string containing "WHERE" is not part of the query. A WHERE clause doesn't make sense here anyway, so maybe that is why it is detached. Also, the non-existent "user" table is referenced.

NULL and empty string are not the same thing. That INSERT query does not allow you to insert NULL because, even if $id is NULL according to PHP, the single quotes in the $sql string will make it appear as an empty string to MySQL.
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: Combobox

Post by ursl40 »

Sorry, it's users table - I've just written it wrong here, so:
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)

and also:
WHERE users.id = cars.user_id
I deleted WHERE clause.

So, how can I managed that INSERT query would allow me to insert NULL or empty string (no user is selected for new entry of a car)?

I also leave NULL ON DELETE and UPDATE?
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Combobox

Post by McInfo »

ursl40 wrote:So, how can I managed that INSERT query would allow me to insert NULL
You can use the NULL keyword.

Code: Select all

INSERT INTO `cars` (`name`, `user_id`) VALUES ('Who', NULL)
Or you can omit the user_id field and it will insert the default value, which is NULL.

Code: Select all

INSERT INTO `cars` (`name`) VALUES ('Who')
ursl40 wrote:or empty string
You can't insert an empty string into a field of type INT.
ursl40 wrote:I also leave NULL ON DELETE and UPDATE?
When you delete a row in the users table, which of these do you want to happen to rows in the cars table where cars.user_id matches users.id?
  1. Delete the rows.
  2. Set cars.user_id to NULL.
  3. Do nothing. (The query will fail.)
When you change the id of a row in the users table, which of these do you want to happen to rows in the cars table where cars.user_id matches users.id?
  1. Change cars.user_id to match.
  2. Set cars.user_id to NULL.
  3. Do nothing. (The query will fail.)
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: Combobox

Post by ursl40 »

If I use NULL keyword, it will always insert NULL value?, but I want that only if I don't choose user (from combobox) for a car.
shafiq2626
Forum Commoner
Posts: 88
Joined: Wed Mar 04, 2009 1:54 am
Location: Lahore
Contact:

Re: Combobox

Post by shafiq2626 »

Hi!
This can be solve easily. when you are editing any product get user_is at that time and then write like this

Code: Select all

<select name='id' id="combo" onchange="CBtoTB()">

<?php
if(isset($_GET['user_id']))
{
$qry=mysql_query("select * from users where user_id=$_GET[user_id]");
$fetch=mysql_fetch_array($qry);

echo "<option value=\"$fetch[id]\"";

echo " selected=\"selected\"";

echo ">$fetch[surname]</option>";

}

while ($row = mysql_fetch_array($query)) {
$id = $row['id'];
$name = $row['name'];
$surname = $row['surname'];

echo "<option value=\"{$id}\"";

if ( $id == $line7 ) {
echo " selected=\"selected\"";
}

echo ">$name $surname</option>";
}
?>
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: Combobox

Post by ursl40 »

shafiq2626, Your code isn't fine for me, because I don't get 'Choose user:' in combobox - I need this for a case, if I don't choose user for computer...

I've added <option value="">Choose user:</option>

But it still gives me an error, if I don't choose user...
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Combobox

Post by McInfo »

ursl40 wrote:If I use NULL keyword, it will always insert NULL value?
Yes...
ursl40 wrote:but I want that only if I don't choose user [...] for a car.
Use PHP to build the query string dynamically. If $id is not empty, insert the id. If $id is empty, insert NULL instead, or leave that field out of the query.
ursl40 wrote:(from combobox)
You don't have a combo box. You have a drop-down list.
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: Combobox

Post by ursl40 »

OK, I managed with EMPTY/NOT EMPTY id, thanks!
Post Reply