While loop with a SQL query within a presentation of another query?

[defroster]

Hello,

 

1. I have a page called video.php, where people can view a video of a specific id. (video.php?id=1)

2. Within this page if 'logged in administrator' should be possible to edit title and category

3. Now I need to do a loop to receive all the categories from my table called category so I have the possibility to change category.

 

Table: categories                 Table: videos 
------------------                    -------------------
id (int)                                 id (int)
cat_id (int)                          cat_id(int)
cat (varchar)                       title (varchar)

 

 

I was trying to do something like below but I am not doing it right. How do I do this query? I have been trying like the code below using to queries ($sql and $sql2). I am suspecting I need to use JOIN query or similar instead of having two queries? Help is very

 

<?php

$db = mysql_connect($dbhost, $dbuser, $dbpassword);
mysql_select_db($dbdatabase, $db);

$sql = "Select videos.*, categories.cat FROM videos, categories
WHERE videos.id =" . $_GET['id'];

$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);


	echo "<h2><a href='video.php?id=" . $row['id'] . "'>" .$row['title'] . "</a></h2>";


	//Admin stuff Users.Setting=1 
	if($_SESSION['setting'] == 1) {
	echo "<form name='update' method='post' action='update.php'><input type='hidden' name='id' value='". $row['id']. "'>";
	echo "<input type='text' name='title' value='". $row['title'] . "' size='100'><br>";

	$sql2 = "Select * FROM categories";
	$result2 = mysql_query($sql2) or die(mysql_error());
	$row2 = mysql_fetch_assoc($result2);		

	echo "<SELECT NAME='categories'>";

		while($row2 = mysql_fetch_array($result2));
		{
		echo "<OPTION VALUE='". $row2['id'] ."'>". $row2['cat'];

		}

	echo "</SELECT>";
	echo "<input type='submit' name='update' value='update'><br>";
	}
?>
-- VIDEO IS DISPLAYED HERE ----
<?php
	echo "<br><i>In <a href='cat.php?id=" . $row['cat_id'] ."'>" .$row['cat']. "</a>";
	echo "<hr>";

?>

 

Help is very much appreciated. THanks /df

[PFMaBiSmAd]

$sql2 = "Select * FROM categories";

 

^^^ The output from that query is static. You would not put it inside of a loop that is processing contents from your main query. You would in fact execute that query one time and form the <select> ... </select> menu in a variable, then simply echo that variable where you want it.

[defroster]

Ok, so my understanding is that I am doing the correct thing then? Please confirm.

 

The code I am using however generates nothing, as the <option value=''> just is blank.. Must be doing something wrong

 

Thanks so much for help!

[defroster]

I have been trying everything. The code seems to run fine but the output is completely blank. Any ideas? Thank you so much /df

[PFMaBiSmAd]

Cannot really help you without seeing the code responsible for the symptoms and the actual 'view source' of the output would be helpful.

 

If there are the correct number of <option> </option> tags but the value or the display text is empty, that would imply using a wrong variable name/index name in your code.

[defroster]

Thanks again for trying to help me out! Here is the full code and the html output

 

<?php
session_start();
require ("incl/config.php");
require ("incl/functions.php");

if(isset($_GET['id']) == TRUE) {
if(is_numeric($_GET['id']) == FALSE) {
	$error = 1; // There is no NUMERIC id received from the GET /LX
}

if($error == 1) {
	header("Location: " . $config_basedir); // Redirect to start page /LX
}
else {
	$validentry = $_GET['id']; // Continue and get the id to displayvideo /LX
}
}
else {
$validentry = 0;
header("Location: " . $config_basedir); // Added LX redirect to start page /LX
}

require("incl/header.php");
require("incl/menu.php");
?>
<!------------------------------ HEADER ------------------------------------->

<?php

$db = mysql_connect($dbhost, $dbuser, $dbpassword);
mysql_select_db($dbdatabase, $db);

$sql = "Select videos.*, categories.cat FROM videos, categories
WHERE videos.id =" . $_GET['id'];

$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);


	echo "<h2><a href='video.php?id=" . $row['id'] . "'>" .$row['title'] . "</a></h2>";
	echo "Posted " . timeAgo($row['dateposted'],$dateto=-1);
	echo "<h4>". $row['titletext'] ."</h4>";

	//Admin stuff Users.Setting=1 /LX
	if($_SESSION['setting'] == 1) {
	echo "<form name='update' method='post' action='update.php'><input type='hidden' name='id' value='". $row['id']. "'>";
	echo "<input type='text' name='dateposted' value='". $row['dateposted'] . "'  size='25'>  "; 
	echo "<input type='text' name='url' value='". $row['url'] . "' size='25'><br>";  
	echo "<input type='text' name='title' value='". $row['title'] . "' size='100'><br>";
	echo "<input type='text' name='titletext' value='". $row['titletext'] . "' size='100'><br>";


	$sql2 = "Select * FROM categories";
	$result2 = mysql_query($sql2) or die(mysql_error());
	$row2 = mysql_fetch_assoc($result2);		

	echo "<SELECT NAME='categories'>";

		while($row2 = mysql_fetch_array($result2));
		{
		echo "<OPTION VALUE='". $row2['id'] ."'>". $row2['cat'];

		}

	echo "</SELECT>";
	echo "<input type='submit' name='update' value='update'><br>";
	}

	?>
	<object width="560" height="340"><param name="movie" value="http://www.youtube.com/v/<?= $row['url']; ?>&hl=en_US&fs=1"></param><param name="allowFullScreen" value="true"></param><param name="allowscriptaccess" value="always"></param><embed src="http://www.youtube.com/v/<?= $row['url']; ?>&hl=en_US&fs=1" type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="560" height="340"></embed></object>
	<?php
	echo "<br><i>In <a href='cat.php?id=" . $row['cat_id'] ."'>" .$row['cat']. "</a>";
	echo "<hr>";

?>

<!------------------------------ FOOTER ------------------------------------->
<?php
require("incl/footer.php");
?>

 

 

And the output.

 

<html>

<head>
<title>title</title>
<link rel="stylesheet" href="incl/stylesheet.css" type="text/css" />
</head>

<body>
<h1>title</h1>


- <a href="index.php">home</a> -

<a href='logout.php'>admin logout </a>
<hr><!------------------------------ HEADER ------------------------------------->

<h2><a href='video.php?id=3'>Awesome baseball commentary after girl gets glove in face</a></h2>Posted 2
days ago<h4>Haaha</h4><form name='update' method='post' action='update.php'><input type='hidden' name='id' value='3'><input type='text' name='dateposted' value='2010-07-24 17:45:00'  size='25'>  <input type='text' name='url' value='3lsNb5rbuFY' size='25'><br><input type='text' name='title' value='Awesome baseball commentary after girl gets glove in face' size='100'><br><input type='text' name='titletext' value='Haaha' size='100'><br><SELECT NAME='categories'><OPTION VALUE=''></SELECT><input type='submit' name='update' value='update'><br>		<object width="560" height="340"><param name="movie" value="http://www.youtube.com/v/3lsNb5rbuFY&hl=en_US&fs=1"></param><param name="allowFullScreen" value="true"></param><param name="allowscriptaccess" value="always"></param><embed src="http://www.youtube.com/v/3lsNb5rbuFY&hl=en_US&fs=1" type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="560" height="340"></embed></object>
	<br><i>In <a href='cat.php?id=3'>music</a><hr>
<!------------------------------ FOOTER ------------------------------------->
<hr><br>

</body>
</html>

 

[PFMaBiSmAd]

A) So you didn't move the <select> ... </select> query/logic so it would only be executed once and the output reused where needed,

B) You are fetching and discarding the first row from the result set in the following line of code- $row2 = mysql_fetch_assoc($result2); Why do you have that line of code in your program?,

C) I either doubt the names of your columns in the categories table are 'id' and 'cat' and/or there is no data in those columns.

 

 

[defroster]

A) So you didn't move the <select> ... </select> query/logic so it would only be executed once and the output reused where needed,

B) You are fetching and discarding the first row from the result set in the following line of code- $row2 = mysql_fetch_assoc($result2); Why do you have that line of code in your program?,

C) I either doubt the names of your columns in the categories table are 'id' and 'cat' and/or there is no data in those columns.

 

Thanks PFMaBiSmAd,

 

So at the moment I am a little bit confused. To answer your questions

 

A) My understanding is that I loop through the <option> fields to be able to list all the different categories, so there should only be one <SELECT> before and a </SELECT> after the while loop. Is that what you meant?

 

B) I thought I needed that row to be able to do a second query, but how do I then do a second query to ask for all the different categories?

 

C) I have double and triple checked but the table name is categories, and the id & cat exist with and contain data so I'm a bit troubled by this one..

 

Sorry if I seem a bit thick about this but I am trying to learn and hopefully I will become better.

/df