[SOLVED] MySQL data retrieving

[wrathican]

hey guys im usually competant at retrieving data from a db, but in this case im not so sure.

what i wanna do is select a single column data from a certain row, heres me query:

$query = "SELECT course_id FROM cy_course WHERE course_title='" . $title . "'";

 

what im unsure about is what to do next?

how do i put that one value in a variable?

 

thanks

[DeadEvil]

Try this one it will return single row.

 

<?

$query = "SELECT course_id FROM cy_course WHERE course_title='" . $title . "'";

$result = mysql_fetch_assoc($query);

print $result[course_id];

?>

[wrathican]

bah!

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in F:\wamp\www\CycleYorkshire\cms\course.php on line 209

tha means my query is failing right?

 

hmm, i dont see why it would fail

as the $title variable im using is coming from a form that was submitted before that.

however before i use it in that situation i am inserting it into the table im trying to get it from

 

 

what happens is i add a course to the table, using a form and the values from the form. since any good structured table has an id column that auto increments i want to get the id for the newly inserted row. so i thought i would try and get it by using that select query in the above post. i thought about using that variable because it has just come from the previous form and i know its in the DB. can i use that variable again?

[DeadEvil]

ohh I forgot the mysql_query function. 

 

$query = "SELECT course_id FROM cy_course WHERE course_title='" . $title . "'";
$result = mysql_fetch_assoc(mysql_query($query));
print $result[course_id];

[wrathican]

*head palms*

of course, i shudda noticed that one

 

thanks very much for your help dude

[wrathican]

hmm my next insert query doesnt work...

heres my code:

$title = $_POST['title'];
	  $desc = $_POST['description'];
	  $price = $_POST['price'];
	  $level = $_POST['level'];
	  $location = $_POST['location'];
	  //inserts the new stuff into the db
	  $query = "INSERT INTO cy_course (course_title, course_description, course_price, course_level, course_location) values ('$title', '$desc', '$price', '$level', '$location')";
	  
	  mysql_query($query) or die(mysql_error().'<br />Query:'.$query);
	  //gets the id of the newly created course and passes it to the new insert query
	  $query = "SELECT course_id FROM cy_course WHERE course_title='" . $title . "'";
	  
	  $result = mysql_fetch_assoc(mysql_query($query));
	  
	  $course_id = $result[course_id];
	  //inserts the newly added course info into the cy_list table
	  $query = "INSERT INTO cy_list (li_tour_id, li_title, li_type, li_price) values ('$course_id', '$title', 'course', '$price'";
	  
	  $result = mysql_query($query);
	  
	  if(!$result)
	  {
	  echo "Oops. Something has gone wrong please try again later";
	  }else{
	  
	  echo 'Thank you. The course has been created. Please go <a href="cms.php">back</a> and select a new option.';
	  }

 

any ideas whats up? the query just doest exceute

[Wildbug]

For returning a single value from a MySQL query, I like the mysql_result function, as it saves a line of code.

 

<?php
$result = mysql_query("SELECT course_id FROM cy_course WHERE course_title='$title'");
$value = mysql_result($result,0);
?>

 

As far as your second problem, you're missing a closing parenthesis on the second INSERT statement.  Use mysql_errno/mysql_error() to debug these.

[wrathican]

yeah i noticed the closing parenthesis, cheers fpor the reply though

[DeadEvil]

			$course = $_POST['course'];
	$title = $_POST['title'];
	  $desc = $_POST['description'];
	  $price = $_POST['price'];
	  $level = $_POST['level'];
	  $location = $_POST['location'];
	  //inserts the new stuff into the db
	  $query = "INSERT INTO cy_course (course_title, course_description, course_price, course_level, course_location) values ('$title', '$desc', '$price', '$level', '$location')";
	  
	  mysql_query($query) or die(mysql_error().'<br />Query:'.$query);
	  //gets the id of the newly created course and passes it to the new insert query
	  # use this function of retrieving newly insert ID.
	  $new_id = mysql_insert_id();

	  //inserts the newly added course info into the cy_list table
	  $query = "INSERT INTO cy_list (li_tour_id, li_title, li_type, li_price) values ('$new_id', '$title', '$course', '$price'";
	  
	  $result = mysql_query($query);
	  
	  if(!$result)
	  {
	  echo "Oops. Something has gone wrong please try again later";
	  }else{
	  
	  echo 'Thank you. The course has been created. Please go <a href="cms.php">back</a> and select a new option.';
	  }