Combobox
Moderator: General Moderators
Re: Combobox
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)?
Re: Combobox
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.)
(You don't have a "combo" box.)
Re: Combobox
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?
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?
Re: Combobox
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.
Re: Combobox
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)'
'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)'
Re: Combobox
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.
Re: Combobox
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";
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";
Re: Combobox
A table is created with the name "users", but the constraint references "user".
There are two PHP statements here.
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.
Code: Select all
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
# ^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?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.
Re: Combobox
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?
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?
Re: Combobox
You can use the NULL keyword.ursl40 wrote:So, how can I managed that INSERT query would allow me to insert NULL
Code: Select all
INSERT INTO `cars` (`name`, `user_id`) VALUES ('Who', NULL)Code: Select all
INSERT INTO `cars` (`name`) VALUES ('Who')You can't insert an empty string into a field of type INT.ursl40 wrote:or empty string
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?ursl40 wrote:I also leave NULL ON DELETE and UPDATE?
- Delete the rows.
- Set cars.user_id to NULL.
- Do nothing. (The query will fail.)
- Change cars.user_id to match.
- Set cars.user_id to NULL.
- Do nothing. (The query will fail.)
Re: Combobox
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
Hi!
This can be solve easily. when you are editing any product get user_is at that time and then write like this
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>";
}
?>Re: Combobox
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...
I've added <option value="">Choose user:</option>
But it still gives me an error, if I don't choose user...
Re: Combobox
Yes...ursl40 wrote:If I use NULL keyword, it will always insert NULL value?
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:but I want that only if I don't choose user [...] for a car.
You don't have a combo box. You have a drop-down list.ursl40 wrote:(from combobox)
Re: Combobox
OK, I managed with EMPTY/NOT EMPTY id, thanks!