Page 1 of 1

How to display single data values from a MySQL table with PH

Posted: Tue Mar 08, 2011 12:31 pm
by mareksl
I am currently making a video website using MySQL databases and PHP. The info about the videos is stored in a table:

ID / filename / size / title / description / uploader / date / length / rating / category / image / views

I have several places where I want to display the data from the table, but only the specific data concerning the corresponding video and only from specific cells in specific places. For example on the page displaying the video, I want to have the video title over the video and the description under it (similar to YouTube). Or for example a search function, where the user searches for a video based on Title/Descriptio, but all the Info gets displayed, but in a certain form.
I do not really know PHP, but I learn as I go. Unfortunately I have not found any tutorials showing how this can be made, neither any finished examples. I have found exapmles of search scripts, but they only show the position of the row in the table. And examples that display a certain row, display the whole row, but not certain cells. I need the separate cells and want to be able to position them freely.
Thanks in advance, you can post links with tutorials or ready examples if you have any.

And thank you for not posting replies like: "Use google" or "Search this forum". I am tired of searching and want to ask personally. :banghead: Thanks!

Re: How to display single data values from a MySQL table wit

Posted: Tue Mar 08, 2011 1:59 pm
by Jonah Bron
You need the WHERE clause for your query. Here's an example:

Code: Select all

SELECT ID, filename, size, title, description, uploader, date, length, rating, category, image, views FROM videos_table WHERE ID = 3
That will grab only the row with the ID of 3. So for example, someone could visit your site at http://example.com/video.php?id=28. The number is in $_GET['id']. Clean it with mysql_real_escape_string(), and pop that into your query.

More info here: http://www.w3schools.com/php/php_mysql_where.asp

Re: How to display single data values from a MySQL table wit

Posted: Tue Mar 08, 2011 2:11 pm
by mareksl
Ok thanks a lot, ill try it! ;)
I may have another question though, but let me first try this.

Re: How to display single data values from a MySQL table wit

Posted: Tue Mar 08, 2011 2:53 pm
by mareksl
Can my code on the search page for example look like this?

Code: Select all

<?php
$search = $_GET["s"];
$con = mysql_connect("localhost","root","marmark");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db_kitchentube", $con);

$result = mysql_query("SELECT * FROM videos
WHERE title = $search");

while($row = mysql_fetch_array($result))
  {
  echo $row['title'] . " " . $row['description'];
  echo "<br />";
  }
?>
Form:

Code: Select all

<form action="search.php" method="get">
<input class="search" value="Search" onfocus="if (this.value==this.defaultValue) this.value='';" name="s"/>
<input class="searcha" type="submit" value="Search" />
</form>
It gives me an error: "Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in D:\xampp\htdocs\KitchenTube\search.php on line 48" (line 48 is while($row = mysql_fetch_array($result)))

Sorry if this is some stupid mistake, I'm a beginner. :oops:

Re: How to display single data values from a MySQL table wit

Posted: Tue Mar 08, 2011 4:25 pm
by Jonah Bron
It states in the manual,
Manual wrote:For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.
This means there's a syntax error your SQL. The problem is that you need quotes around your string.

Code: Select all

$result = mysql_query("SELECT * FROM videos WHERE title = \"$search\"");
Also note that you're trying to search with an exact match. Basically what your query says is "get all rows who's title exactly matches this". I'd recommend you start with something simpler than a search, like creating a list of all videos, each with a link to the video itself. That would involve querying for all rows, generating links to each with the ID of the video in the URL, and creating another page that gets that ID and shows the video.

Re: How to display single data values from a MySQL table wit

Posted: Tue Mar 08, 2011 4:37 pm
by mareksl
Thanks a lot, it fixed that issue!

And yeah, it's stupid to search for an exact match, as people rather search keywords or certain words. I will probably return to this later, after acquiring some knowledge. xD

Thanks for the advice, I'll do it step by step. :D