Page 2 of 2

Re: Combobox

Posted: Fri Nov 26, 2010 7:07 am
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)?

Re: Combobox

Posted: Fri Nov 26, 2010 11:14 am
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.)

Re: Combobox

Posted: Mon Nov 29, 2010 12:17 am
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?

Re: Combobox

Posted: Mon Nov 29, 2010 1:41 pm
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.

Re: Combobox

Posted: Wed Dec 08, 2010 12:22 am
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)'

Re: Combobox

Posted: Wed Dec 08, 2010 2:44 pm
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.

Re: Combobox

Posted: Thu Dec 09, 2010 12:24 am
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";

Re: Combobox

Posted: Thu Dec 09, 2010 1:08 pm
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.

Re: Combobox

Posted: Fri Dec 10, 2010 12:10 am
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?

Re: Combobox

Posted: Fri Dec 10, 2010 10:21 am
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.)

Re: Combobox

Posted: Mon Dec 13, 2010 12:03 am
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.

Re: Combobox

Posted: Mon Dec 13, 2010 2:14 am
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>";
}
?>

Re: Combobox

Posted: Mon Dec 13, 2010 2:59 am
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...

Re: Combobox

Posted: Mon Dec 13, 2010 12:31 pm
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.

Re: Combobox

Posted: Tue Dec 14, 2010 12:51 am
by ursl40
OK, I managed with EMPTY/NOT EMPTY id, thanks!