Hi there, I'm having a problem getting values inserting properly. What I'm doing is inserting values into a table with an auto incremented key, I then use select to return this key and then I want to insert that into a few more relation tables. But its inserting 0 instead of the proper value. The value is printing out fine on its own so I'm not sure what's going wrong. I've also been trying to find a more elegant way to do those queries, it's a rough draft :P

<?php
	ini_set('session.cache_limiter','private');
	session_start();
	if(isset($_POST['submitbutton'])){
		$title=$_POST['title'];
		$author=$_POST['author'];
		$bio=$_POST['bio'];
		//$ISBN=$_POST['ISBN'];
		$libref=$_POST['libref'];
		//$series=$_POST['series'];
		//$volume=$_POST['volume'];
	//	$publisher=$_POST['publisher'];
	//	$year=$_POST['year'];
	//	$status=$_POST['status'];
	//	$section=$_POST['section'];
		$summary=$_POST['summary'];
	@mysql_connect($_SESSION['hostname'],$_SESSION['username'],$_SESSION['password']) or die("Access to db server denied");
	@mysql_select_db($_SESSION['db']) or die("Access to database denied");
	
	mysql_query("insert ignore into authors(aname, abio) values('$author', '$bio')") or die(mysql_error());
		
	
	$aid=@mysql_query("select aid from authors where aname = '$author'");
	echo mysql_result($aid, 1);
	mysql_query("insert ignore into books(title, summary, quantity) values('$title', '$summary', '1')") or die(mysql_error());
	print("added books");
	
	$bid=@mysql_query("select bid from books where title = '$title'")or die(mysql_error());
	echo mysql_result($bid, 1);
	//@mysql_query("insert ignore into ISBN values('$ISBN')")or die(mysql_error());
	mysql_query("insert ignore into libraryReference values('$libref')")or die(mysql_error());
	mysql_query("insert ignore into librarybook values('$libref', '$bid')")or die(mysql_error());
	//mysql_query("insert ignore into hasISBN values('$libref', '$ISBN')")or die(mysql_error());
	//mysql_query("insert ignore into bookFormat values('$libref', '$fid')")or die(mysql_error());
	//$secID=@mysql_query("select secID from Section where Section = '$section'");
	//mysql_query("insert ignore into areInSection('$libref', '$secID')")or die(mysql_error());
	//$sid=@mysql_query("select sid from status where status = '$status'");
	//mysql_query("insert ignore into areAvailable values('$libref', '$sid')")or die(mysql_error());
	mysql_query("insert ignore into authorsWrite values('$aid', '$libref')")or die(mysql_error());
	//mysql_query("insert ignore into publishers(name) values ('$publisher')")or die(mysql_error());
	///$pid=@mysql_query("select pid from publishers where pname = '$publisher'")or die(mysql_error());
	//mysql_query("insert ignore into booksPub values('$libref', '$pid', '$year')")or die(mysql_error());
	//mysql_query("insert ignore into authorsPub values('$aid', '$pid')")or die(mysql_error());
	//mysql_query("insert ignore into series(title) values('$series')");
	//$serID = "select serID from series where title = '$series'";
	//mysql_query("insert into areInSeries values ('$libref', '$serID', '$volume')")or die(mysql_error());
	
	}
?>

mysql_query("insert ignore into librarybook values('$libref', '$bid')")or die(mysql_error());

this is the one that's failing, if I select * from librarybook it returns a bid = 0 for every entry. Also the ignore isn't working, I was trying to find a way to stop duplicate entries from being made, ie: if the title is all ready listed in the books table don't enter it a second time. A quick internet search suggested ignore.

i suggest you debug the bad code that you mentioned step by step under MySQL query (if you are using phpadmin) and see if the result is correct each step
start from select *, then insert, then insert ignore....

hope this helps!

HI, I thought I had this solved and then turned out no. I revamped my tables a little - not sure if it helped at all really. But I'm still getting zero's inserting instead of the actual values. Which is strange because when I print them out to the screen they print the proper values.

Here's the modified code:

<?php
	ini_set('session.cache_limiter','private');
	session_start();
	if(isset($_POST['submitbutton'])){
		$title=$_POST['title'];
		$author=$_POST['author'];
		$bio=$_POST['bio'];
		$ISBN=$_POST['ISBN'];
		$libref=$_POST['libref'];
		$series=$_POST['series'];
		$volume=$_POST['volume'];
		$format=$_POST['format'];
		$publisher=$_POST['publisher'];
		$year=$_POST['year'];
		$status=$_POST['status'];
		$section=$_POST['section'];
		$summary=$_POST['summary'];
	@mysql_connect($_SESSION['hostname'],$_SESSION['username'],$_SESSION['password']) or die(@mysql_error());
	@mysql_select_db($_SESSION['db']) or die("Access to database denied");
	
	mysql_query("insert ignore into authors(aname, abio) values('$author', '$bio')") or die(mysql_error());
	$aid=mysql_query("select aid from authors where aname = '$author'");
	
	mysql_query("insert ignore into books(title, summary) values('$title', '$summary')") or die(mysql_error());
	$bid=mysql_query("select bid from books where title = '$title'");
	
	
	mysql_query("insert into libraryreference values ('$libref')") or die(mysql_error());
	
	mysql_query("insert ignore into series(title) values('$series')") or die(mysql_error());
	$serID =mysql_query("select serID from series where title = '$series'");
	
	mysql_query("insert ignore into publishers(pname) values('$publisher')") or die(mysql_error());
	$pid=mysql_query("select pid from publishers where pname = '$publisher'");
	
	$sid =mysql_query("select sid from status where status = '$status'");
	print("section ID");
	$secID=mysql_query("select sectionID from Section where Section = '$section')");
	$fid=mysql_query("select fid from format where formattype = '$format')");
	
	mysql_query("insert into librarybook values('$libref', '$bid', '$sid', '$aid', '$pid', '$serID', '$fid', '$volume', '$ISBN', '$year')");
	
	}
?>

All of the id values are auto incremented, and if I print the value to the screen I get back the proper value its just for some reason inserting them the way I am in the last query is inserting zeros...

are you talking about this last query line of code?

mysql_query("insert into librarybook values('$libref', '$bid', '$sid', '$aid', '$pid', '$serID', '$fid', '$volume', '$ISBN', '$year')");


if so, try
echo $libref;

and
echo '$libref';


What are differences?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.