11 Replies - 1179 Views - Last Post: 21 August 2011 - 05:04 PM Rate Topic: -----

#1 iamcenz  Icon User is offline

  • You wish you were my hand!
  • member icon

Reputation: 7
  • View blog
  • Posts: 2,442
  • Joined: 26-March 01

Could not insert data into DB: Cannot add or update a child row:

Posted 20 August 2011 - 04:24 PM

I keep getting a "Could not insert data into DB: Cannot add or update a child row: " error and i cant figure out why.


--
-- Table structure for table `artists`
--

CREATE TABLE IF NOT EXISTS `artists` (
  `artist_id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `pic_loc` varchar(150) NOT NULL,
  `genre` varchar(100) NOT NULL,
  `location` varchar(150) NOT NULL,
  `inspirations` text NOT NULL,
  `fb_www` varchar(50) NOT NULL,
  `ms_www` varchar(50) NOT NULL,
  `www` varchar(50) NOT NULL,
  PRIMARY KEY (`artist_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `work`
--

CREATE TABLE IF NOT EXISTS `work` (
  `work_id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `artist_id` int(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `title` varchar(50) NOT NULL,
  `work_loc` varchar(150) NOT NULL,
  PRIMARY KEY (`song_id`),
  KEY `user_id` (`user_id`),
  KEY `artist_id` (`artist_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(128) NOT NULL,
  `email` varchar(255) NOT NULL,
  `permission` int(1) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `artists`
--
ALTER TABLE `artists`
  ADD CONSTRAINT `artists_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `work`
--
ALTER TABLE `songs`
  ADD CONSTRAINT `work_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
  ADD CONSTRAINT `work_ibfk_2` FOREIGN KEY (`artist_id`) REFERENCES `artists` (`artist_id`);



this is my form.
<form action="create.php" method="post" enctype="multipart/form-data" name="artist" id="artist">
        <div id="band_name">
          <input type="text" name="name" id="name" />
          artist/band name *
          <input name="user_id" type="hidden" value="<? $_SESSION['user_id'] ?>" />
        </div>
        <br />
        <div id="pic_upload">
          <input type="file" name="pic_loc" id="pic_loc" />
          profile picture<input type="hidden" name="MAX_FILE_SIZE" value="100000" /></div>
        <br />
        <div id="genre">
          <select name="genre" id="genre">
            <option value="Rock" selected="selected">Rock</option>
            <option value="Metal">Metal</option>
            <option value="Hardcore">Hardcore</option>
            <option value="Hip-Hop/Rap">Hip-Hop/Rap</option>
            <option value="R&amp;B">R&amp;B</option>
          </select>
          genre *</div>
        <br />
        <div id="location">
          <input type="text" name="location" id="location" />
          location *</div>
          <br />
        <div id="insperations">
          <input type="text" name="insperations" id="insperations" />
          inspirations *</div>
        <br />
        <div id="fb_www">
          <input name="fb_www" type="text" id="fb_www" value="www.facebook.com/" />
          facebook </div>
        <br />
        <div id="ms_www">
          <input name="ms_www" type="text" id="ms_www" value="www.myspace.com/" />
          myspace</div>
        <br />
        <div id="www">
          <input name="www" type="text" id="www" value="www." />
          offical website </div>
        <br />
        <div id="submit">
          <input name="submit" type="submit" id="submit" value="submit" />
        </div>
      </form>


and my create.php

<?php
// Start a session for error reporting
session_start();

// Call our connection file
require("functions.php");

// Check to see if the type of file uploaded is a valid image type
function is_valid_type($file)
{
	// This is an array that holds all the valid image MIME types
	$valid_types = array("image/jpg", "image/jpeg", "image/bmp", "image/gif");

	if (in_array($file['type'], $valid_types))
		return 1;
	return 0;
}

// Just a short function that prints out the contents of an array in a manner that's easy to read
// I used this function during debugging but it serves no purpose at run time for this example
function showContents($array)
{
	echo "<pre>";
	print_r($array);
	echo "</pre>";
}

// Set some constants

// This variable is the path to the image folder where all the images are going to be stored
// Note that there is a trailing forward slash
$TARGET_PATH = "artist_pics/";

// Get our POSTed variables
$name = $_POST['name'];
$user_id = $_SESSION['user_id'];
$pic_loc = $_FILES['pic_loc'];
$genre = $_POST['genre'];
$location = $_POST['location'];
$inspirations = $_POST['insperations'];
$fb_www = $_POST['fb_www'];
$ms_www = $_POST['ms_www'];
$www = $_POST['www'];

// Sanitize our inputs
$name = mysql_real_escape_string($name);
$user_id = mysql_real_escape_string($user_id);
$pic_loc['name'] = mysql_real_escape_string($pic_loc['name']);
$genre = mysql_real_escape_string($genre);
$location = mysql_real_escape_string($location);
$inspirations = mysql_real_escape_string($inspirations);
$fb_www = mysql_real_escape_string($fb_www);
$ms_www = mysql_real_escape_string($ms_www);
$www = mysql_real_escape_string($www);

// Build our target path full string.  This is where the file will be moved do
// i.e.  images/picture.jpg
$TARGET_PATH .= $pic_loc['name'];

// Make sure all the fields from the form have inputs
if ( $name == "" || $user_id == "" || $genre == "" || $location == "" || $inspirations == "" )
{
	$_SESSION['error'] = "All * fields are required";
	echo $_SESSION['error'];
	exit;
}

// Check to make sure that our file is actually an image
// You check the file type instead of the extension because the extension can easily be faked
if (!is_valid_type($pic_loc))
{
	$_SESSION['error'] = "You must upload a jpeg, gif, or bmp";
	echo $_SESSION['error'];
	exit;
}

// Here we check to see if a file with that name already exists
// You could get past filename problems by appending a timestamp to the filename and then continuing
if (file_exists($TARGET_PATH))
{
	$_SESSION['error'] = "A file with that name already exists";
	echo $_SESSION['error'];
	exit;
}

// Lets attempt to move the file from its temporary directory to its new home
if (move_uploaded_file($pic_loc['tmp_name'], $TARGET_PATH))
{
	// NOTE: This is where a lot of people make mistakes.
	// We are *not* putting the image into the database; we are putting a reference to the file's location on the server
	$sql = "insert into artists (name, user_id, pic_loc, genre, location, inspirations, fb_www, ms_www, www) values ('$name', '$user_id', '" . $pic_loc['name'] . "', '$genre', '$location', '$inspirations', '$fb_www', '$ms_www', '$www')";
	$result = mysql_query($sql) or die ("Could not insert data into DB: " . mysql_error());
	header("Location: view_profile.php?id=$user_id");
	exit;
}
else
{
	// A common cause of file moving failures is because of bad permissions on the directory attempting to be written to
	// Make sure you chmod the directory to be writeable
	$_SESSION['error'] = "Could not upload file.  Check read/write persmissions on the directory";
	echo $_SESSION['error'];
	exit;
}
?>




Is This A Good Question/Topic? 0
  • +

Replies To: Could not insert data into DB: Cannot add or update a child row:

#2 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: Could not insert data into DB: Cannot add or update a child row:

Posted 20 August 2011 - 04:34 PM

That sounds like a foreign key constraint error. Judging from your schema, I'd guess the user_id you're inserting into the artists table most likely doesn't exist in your users table.
Was This Post Helpful? 0
  • +
  • -

#3 aaron1178  Icon User is offline

  • Dovakiin, Dragonborn
  • member icon

Reputation: 169
  • View blog
  • Posts: 1,298
  • Joined: 22-October 08

Re: Could not insert data into DB: Cannot add or update a child row:

Posted 20 August 2011 - 05:10 PM

How do you get 201 kudos :/
Was This Post Helpful? 0
  • +
  • -

#4 iamcenz  Icon User is offline

  • You wish you were my hand!
  • member icon

Reputation: 7
  • View blog
  • Posts: 2,442
  • Joined: 26-March 01

Re: Could not insert data into DB: Cannot add or update a child row:

Posted 20 August 2011 - 05:11 PM

its there, its pulling from the session id
Was This Post Helpful? 1
  • +
  • -

#5 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6062
  • View blog
  • Posts: 23,513
  • Joined: 23-August 08

Re: Could not insert data into DB: Cannot add or update a child row:

Posted 21 August 2011 - 06:45 AM

<input name="user_id" type="hidden" value="<? $_SESSION['user_id'] ?>" />


Is there a reason you're adding the user ID as a hidden field? That's generally not needed...it's already IN THE SESSION.

// Get our POSTed variables
$name = $_POST['name'];
$user_id = $_SESSION['user_id'];
$pic_loc = $_FILES['pic_loc'];
$genre = $_POST['genre'];
$location = $_POST['location'];
$inspirations = $_POST['insperations'];
$fb_www = $_POST['fb_www'];
$ms_www = $_POST['ms_www'];
$www = $_POST['www'];

// Sanitize our inputs
$name = mysql_real_escape_string($name);
$user_id = mysql_real_escape_string($user_id);
$pic_loc['name'] = mysql_real_escape_string($pic_loc['name']);
$genre = mysql_real_escape_string($genre);
$location = mysql_real_escape_string($location);
$inspirations = mysql_real_escape_string($inspirations);
$fb_www = mysql_real_escape_string($fb_www);
$ms_www = mysql_real_escape_string($ms_www);
$www = mysql_real_escape_string($www);



You're going through unnecessary machinations here. Skip the intermediate assignment and just directly sanitize the $_POST variables.

$sql = "insert into artists (name, user_id, pic_loc, genre, location, inspirations, fb_www, ms_www, www) values ('$name', '$user_id', '" . $pic_loc['name'] . "', '$genre', '$location', '$inspirations', '$fb_www', '$ms_www', '$www')";
	$result = mysql_query($sql) or die ("Could not insert data into DB: " . mysql_error());


I'm really getting tired of saying the following, because it's common fucking sense, and if you're a programmer, you have GOT to have common sense:

You are constructing a query from user-inputted data. The query is failing. Doesn't it JUST MAKE SENSE to print out the contents of the failing query when you print the error (at least before you put it into production; in production you would use a logging mechanism), so you can eliminate an improperly-constructed query as a cause?

$sql = "insert into artists (name, user_id, pic_loc, genre, location, inspirations, fb_www, ms_www, www) values ('$name', '$user_id', '" . $pic_loc['name'] . "', '$genre', '$location', '$inspirations', '$fb_www', '$ms_www', '$www')";
	$result = mysql_query($sql) or die ("Query $sql failed: Could not insert data into DB: " . mysql_error());

Was This Post Helpful? -1
  • +
  • -

#6 iamcenz  Icon User is offline

  • You wish you were my hand!
  • member icon

Reputation: 7
  • View blog
  • Posts: 2,442
  • Joined: 26-March 01

Re: Could not insert data into DB: Cannot add or update a child row:

Posted 21 August 2011 - 04:23 PM

wow many I'm sorry I asked a question. I got it working before you responded, and the script is almost 100% from the tutorial on this site.

I understand its annoying when people post the same thing all the time, but that's what message board is and its not like I am not trying to learn, but I am learning as I go. So quit the bitching and lose the attitude if you don't like then don't come to the boards.
Was This Post Helpful? -1
  • +
  • -

#7 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10560
  • View blog
  • Posts: 39,071
  • Joined: 27-December 08

Re: Could not insert data into DB: Cannot add or update a child row:

Posted 21 August 2011 - 04:25 PM

Out of curiosity, what was your solution? Also, you might consider using prepared statements for guarding against SQL injection, especially since the mysql_*() family of functions is deprecated. PDO and MySQLi are the two prominent tools for this. Dormilich has a bunch of PDO tutorials, like this one.

Hope this helps some. :)
Was This Post Helpful? 1
  • +
  • -

#8 aaron1178  Icon User is offline

  • Dovakiin, Dragonborn
  • member icon

Reputation: 169
  • View blog
  • Posts: 1,298
  • Joined: 22-October 08

Re: Could not insert data into DB: Cannot add or update a child row:

Posted 21 August 2011 - 04:35 PM

Wow, Down Rep me because I asked how do you get 201 kudos :gun_bandana: As kudos only go up by increments of 50 and 25 ;[
Was This Post Helpful? 0
  • +
  • -

#9 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10560
  • View blog
  • Posts: 39,071
  • Joined: 27-December 08

Re: Could not insert data into DB: Cannot add or update a child row:

Posted 21 August 2011 - 04:36 PM

@aaron1178: There was a time where kudos were given for other things beyond tutorials and snippets. Really though, a PM or the Site Questions forum would be the more appropriate places to ask about kudos. :)
Was This Post Helpful? 0
  • +
  • -

#10 iamcenz  Icon User is offline

  • You wish you were my hand!
  • member icon

Reputation: 7
  • View blog
  • Posts: 2,442
  • Joined: 26-March 01

Re: Could not insert data into DB: Cannot add or update a child row:

Posted 21 August 2011 - 04:45 PM

View Postaaron1178, on 21 August 2011 - 06:35 PM, said:

Wow, Down Rep me because I asked how do you get 201 kudos :gun_bandana: As kudos only go up by increments of 50 and 25 ;[


my bad, that was not meant, but i couldnt undo it. my mouse wheel is fucked up and it made the screen spazz out.

and macosxnerd. thanks i'll look into the PDO. I dont know much about the sql injection stuff. need to do alot more reading.
Was This Post Helpful? 0
  • +
  • -

#11 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10560
  • View blog
  • Posts: 39,071
  • Joined: 27-December 08

Re: Could not insert data into DB: Cannot add or update a child row:

Posted 21 August 2011 - 04:48 PM

Glad I could help! :)
Was This Post Helpful? 0
  • +
  • -

#12 aaron1178  Icon User is offline

  • Dovakiin, Dragonborn
  • member icon

Reputation: 169
  • View blog
  • Posts: 1,298
  • Joined: 22-October 08

Re: Could not insert data into DB: Cannot add or update a child row:

Posted 21 August 2011 - 05:04 PM

Ah okay Mac :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1