Query and output data from same table in db

phpbnoobb23 · August 14, 2014

Hello, I seem to have some problem with my script that has a goal of outputting data about the file size when a filename is queried.

The sql table name is file

The table columns are as followed: id | name | mime | size

The file name is stored in name. The script that i have that gets the file name is:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">

	<head>
		<meta http-equiv="content-type" content="text/html;charset=utf-8" />
		<meta name="generator" content="Adobe GoLive" />
		<title>File Select</title>
					
	<!--The following script tag downloads a font from the Adobe Edge Web Fonts server for use within the web page. We recommend that you do not modify it.--><script>var __adobewebfontsappname__="dreamweaver"</script><script src="http://use.edgefonts.net/aguafina-script:n4:default.js" type="text/javascript"></script>
</head>

	 <body>
			
		<div id="title">
			  <h3 align="center">File Upload</h3>
			</div>
  <form action="result.php" method="post" name="fileID" target="_self" class="inp" AUTOCOMPLETE="ON">       
 <h1>
 
<!--Input file name-->

	<label for="fileID">File Name: </label>
              <input type="text" name='file1' id='sampleID' list="samp"> </input><br>
             <datalist id="samp">
 
<?php
$connect = mysql_connect('localhost', 'root', '');


mysql_select_db("test_db");
$query = mysql_query("SELECT * FROM  `file` ORDER BY  `file`.`name` ASC LIMIT 0 , 30");
WHILE ($rows = mysql_fetch_array($query)):
   $File_name = $rows['name'];  
  
echo "<option value=$File_name>$File_name/option> <br>";
endwhile;
?>
</datalist>

<input type="submit" class="button" >
</form>
</body>
</html>

As you can see, I got the file name, but I just can't figure out how to display the file size. The file size is store in size. Just to clarify, I need to search a file name in the database and then output the file size also stored in the database under the same table name "file."

Ch0cu3r · August 14, 2014

The code you posted, only lists files in a <datalist></datalist>.

When and where do you want the filesize to be shown? When some selects a file from the datalist and submits the form? What code have you tried so far.

phpbnoobb23 · August 14, 2014

The code you posted, only lists files in a <datalist></datalist>.

When and where do you want the filesize to be shown? When some selects a file from the datalist and submits the form? What code have you tried so far.

Yes, so when i press submit I would like to pull the file size information from the table.

I have tried something like this:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>GeneSeq -- Result Summary</title>
<link href="CSS/oneColLiqCtrHdr.css" rel="stylesheet" type="text/css" />
</head>

<body>
<div class="container">  
  </p></div>
    <p>
<table width="95%" align="center" class="tableBorder" name="results" cellpadding="5px">
   	<thead align="center" >
        <th width="5%" class='content'>File_Size</th>
     	</thead> 
<?php
	
$File_name  = htmlspecialchars($_POST['File_name']);
$connect = mysql_connect('localhost', 'root', '');


mysql_select_db("test_db");
If (strlen($File_name) == 0) {
$query = mysql_query("SELECT * FROM  `file` ORDER BY  `file`.`size` ASC LIMIT 0 , 30 "); 

WHILE ($rows = mysql_fetch_array($query)):
   $size = $rows['size'];
  
   if (strlen($File_name) == 0) 
echo "No file by that name.";       

$File_Size = preg_replace("/_[0-9]{0,5}$/", "",$size);   
echo "<tr width=60% class='content'>";
echo "<td class='content'>$size<br></td></tr>";



endwhile; 
 
?>
</table>
</p>

    <!-- end .content --></div>
  <div class="footer">
    <p>Example ©2013</p>
    <!-- end .footer --></div>
  <!-- end .container --></div>

</body>
</html>

I'm extremely new to this, so it looks sloppy. I tried putting some stuff together, but it does not work.

I get this error:

Parse error: syntax error, unexpected $end in /home/u293130761/public_html/result.php on line 52

Edited August 14, 2014 by phpbnoobb23

Ch0cu3r · August 14, 2014

Your code has a lot of issues. So we'll start from scratch

To retrieve a specific row from a table you need to use a WHERE clause in your query, eg

$query = mysql_query("SELECT * FROM  `file` WHERE name='filename to search' ");

You'd replace filename to search with the the actual name of the file you want to retrieve the file size for. You could replace it with your $File_name variable. BUT this is very insecure, it will open your code up to SQL Injection attacks (if that wording is new to you then google it, learn about it and how to protect yourself). Instead you'd first need to sanitize $File_name so its value is safe to be used in a query. PHP does have a function called mysql_real_escape_string for doing just that (*see note below).

So now your code for fetching file size for the chosen filename will now read as

$File_name = mysql_real_escape_string($File_name); // sanitize
$result = mysql_query("SELECT name, size FROM  `file` WHERE name='$File_name' "); // use it in the query, fetch the file size for the file.

Then to output the filesize you'd you'd do something like this

if(mysql_num_rows($result))
{
    list($name, $size) = mysql_fetch_row($result); // populate variables with the file name and size
    echo "The file size for <i>$name</i> is <b>$size</b>"; // output info
}
else
{
   echo 'Invalid filename given';
}

NOTE: The mysql_* functions are deprecated, meaning they are no longer supported/maintained and could be removed from future versions of PHP. I would strongly advise you to convert your code over to PDO or MySQLi and use prepared queries when using user input within a query.

phpbnoobb23 · August 14, 2014

Your code has a lot of issues. So we'll start from scratch

To retrieve a specific row from a table you need to use a WHERE clause in your query, eg
$query = mysql_query("SELECT * FROM  `file` WHERE name='filename to search' "); 
You'd replace filename to search with the the actual name of the file you want to retrieve the file size for. You could replace it with your $File_name variable. BUT this is very insecure, it will open your code up to SQL Injection attacks (if that wording is new to you then google it, learn about it and how to protect yourself). Instead you'd first need to sanitize $File_name so its value is safe to be used in a query. PHP does have a function called mysql_real_escape_string for doing just that (*see note below).

So now your code for fetching file size for the chosen filename will now read as
$File_name = mysql_real_escape_string($File_name); // sanitize
$result = mysql_query("SELECT name, size FROM  `file` WHERE name='$File_name' "); // use it in the query, fetch the file size for the file.
Then to output the filesize you'd you'd do something like this
if(mysql_num_rows($result))
{
    list($name, $size) = mysql_fetch_row($result); // populate variables with the file name and size
    echo "The file size for <i>$name</i> is <b>$size</b>"; // output info
}
else
{
   echo 'Invalid filename given';
}
NOTE: The mysql_* functions are deprecated, meaning they are no longer supported/maintained and could be removed from future versions of PHP. I would strongly advise you to convert your code over to PDO or MySQLi and use prepared queries when using user input within a query.

So, it would look something like this?

<?php 

$dbLink = new mysqli('localhost', 'root', '', 'test_db');
$File_name = mysql_real_escape_string($File_name); // sanitize
$result = mysql_query("SELECT name, size FROM  `file` WHERE name='$File_name' "); // use it in the query, fetch the file size for the file.

if(mysql_num_rows($result))
{
    list($name, $size) = mysql_fetch_row($result); // populate variables with the file name and size
    echo "The file size for <i>$name</i> is <b>$size</b>"; // output info
}
else
{
   echo 'Invalid filename given';

}

?>

what about the forms? how would I place them to fetch the file name and then submit to output on the results page? I'm sorry just really new to this.

Edited August 14, 2014 by phpbnoobb23

adam_bray · August 14, 2014

All you've done is change the MySQL connect function but still used the other mysql_* functions - that's inconsistent and doesn't work.

Have a read through this http://codular.com/php-mysqli to get a better understanding of what Ch0cu3r said; or even better, look at the PDO post - http://codular.com/php-pdo-how-to.

Edited August 14, 2014 by adam_bray

phpbnoobb23 · August 18, 2014

The mysql_* functions work fine. I've used them in the past to run little tests and it worked fine.

Edited August 18, 2014 by phpbnoobb23

phpbnoobb23 · August 18, 2014

All you've done is change the MySQL connect function but still used the other mysql_* functions - that's inconsistent and doesn't work.

Have a read through this http://codular.com/php-mysqli to get a better understanding of what Ch0cu3r said; or even better, look at the PDO post - http://codular.com/php-pdo-how-to.

The script below should suffice, correct?

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Result Summary</title>
<link href="CSS/oneColLiqCtrHdr.css" rel="stylesheet" type="text/css" />
</head>

<body>
<div class="container">  
    <p>
<?php
	
$File_name  = htmlspecialchars($_POST['File_name']);
$File_name = mysql_real_escape_string($File_name); // sanitize
$db = new mysqli('localhost', 'root', '', 'test_db');

If (strlen($File_name) == 0) {
$$result = mysql_query("SELECT name, size FROM  `file` WHERE name='$File_name' "); // use it in the query, fetch the file size for the file.

WHILE ($rows = mysql_fetch_array($query)):
   $size = $rows['size'];
  
   if(mysql_num_rows($result))
{
    list($name, $size) = mysql_fetch_row($result); // populate variables with the file name and size
    echo "The file size for <i>$name</i> is <b>$size</b>"; // output info
}
else
{
   echo 'Invalid filename given';
}
?>
</p>

  <div class="footer">
    <p>Results ©2014</p>
    <!-- end .footer --></div>
  <!-- end .container --></div>

</body>
</html>

Ch0cu3r · August 18, 2014

The script below should suffice, correct?

No.

You have clearly ignored what I and adam_bray have said. The links adam gave explains what

There is a fundamental difference between the mysql_ and mysqli_ (note the i )functions. You cannot use them together like you have done. They are separate function libraries. You need to replace all your mysql_ functions to the mysqli_ counterparts. The article adam links to explains how do that.

mac_gyver · August 18, 2014

the last code you posted won't even parse, it's missing the closing syntax of two php constructs. i even see a double $$ on a variable and wrong variable names being used.

programming is an exact science. every character matters. sometimes, capitalization of those characters matters. every variable name matters. every statement, what it does and what it contributes to your goal, matters. copy/pasting code together isn't how programming is accomplished and isn't how learning a programming language works either. you must actually learn what the basic language syntax is, then what each statement you are using means and does, so that you can write code that uses the language syntax and statements to accomplish the goal you are trying to achieve.

next, you need to define the goal you are trying to achieve. besides a statement of what you are trying to accomplish (in this case, process form data to retrieve and display a corresponding piece of information from a database table), you need to define what inputs you have available, what exact processing you want to do based on those inputs, and what exact result or output you want to produce.

i'm not going to take the time to list what's wrong with the posted code because, well, you need to start over with the php portion of your code and write it yourself to do what you want it to do. i have one hint, htmlspecialchars() is an OUTPUT function. it is used when you output data onto a web page. it is not used on input data to a script. any code you may have found posted on the internet that showed using it on data going into a database query was an incorrect application of the function.

phpbnoobb23 · August 18, 2014

No.

You have clearly ignored what I and adam_bray have said. The links adam gave explains what

There is a fundamental difference between the mysql_ and mysqli_ (note the i )functions. You cannot use them together like you have done. They are separate function libraries. You need to replace all your mysql_ functions to the mysqli_ counterparts. The article adam links to explains how do that.

Please excuse my confusion with some of the reading. I tried to cut my code down based on the new mysqli. I do hope I wrote the correct functions.

<?php 

$db = new mysqli('localhost', 'root', '', 'test_db');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

$File_name->real_escape_string($File_name);
$result = mysqli_query("SELECT name, size FROM  `file` WHERE name='$File_name' ");

if(($result->num_rows) = 1) {
list($name, $size) = $result -> fetch_row ();
echo "The file size for <i>$name</i> is <b>$size</b>"; // output info
}
else
{
   echo 'Invalid filename given'; //error

}
$db->close();
?>

Barand · August 18, 2014

Turn error reporting on while you are developing. There are at least four errors in that code.

Ch0cu3r · August 18, 2014

Well that is more like it. But you still have some issues

$File_name->real_escape_string($File_name);

You have got your variables mixed up there, real_escape_string() is a method belonging to the $db object. So $File_name-> should be $db->

Next you are escaping an undefined variable called $File_name. You need to be escaping the $_POST['File_name'] variable here and then save the escaped value to a variable called $File_name

Line 9 should read as

$File_name = $db->real_escape_string($_POST['File_name']);

The next problem is line 12

if(($result->num_rows) = 1) {

You need two equal signs to compare values. The = should be ==

phpbnoobb23 · August 18, 2014

Well that is more like it. But you still have some issues
$File_name->real_escape_string($File_name);
You have got your variables mixed up there, real_escape_string() is a method belonging to the $db object. So $File_name-> should be $db->

Next you are escaping an undefined variable called $File_name. You need to be escaping the $_POST['File_name'] variable here and then save the escaped value to a variable called $File_name

Line 9 should read as
$File_name = $db->real_escape_string($_POST['File_name']);
The next problem is line 12
if(($result->num_rows) = 1) {
You need two equal signs to compare values. The = should be ==

It seems to work fine, but i get an error that says:

Warning: mysqli_query() expects at least 2 parameters, 1 given in /home/u293130761/public_html/result.php on line 10
Invalid filename given.

I understand that the query is for the file name and size, but when i query the file name in search.php I get the error above.

Ch0cu3r · August 18, 2014

Didnt pick that up earlier

You are getting that error because you have called the mysqli_query function using procedural code and have not passed it the $db instance as the first argument. When using mysqli procedurally you must pass in the database instance as the first argument for any function that requires it.

Alternatively you can replace mysqli_query with $db->query

phpbnoobb23 · August 18, 2014

Didnt pick that up earlier

You are getting that error because you have called the mysqli_query function using procedural code and have not passed it the $db instance as the first argument. When using mysqli procedurally you must pass in the database instance as the first argument for any function that requires it.

Alternatively you can replace mysqli_query with $db->query

that fixed the problem and the only error i'm getting now is : Invalid filename given when i do query from search.php

My script for search.php looks like this :

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">

	<head>
		<meta http-equiv="content-type" content="text/html;charset=utf-8" />
		<meta name="generator" content="Adobe GoLive" />
		<title>File Select</title>
					
	<!--The following script tag downloads a font from the Adobe Edge Web Fonts server for use within the web page. We recommend that you do not modify it.--><script>var __adobewebfontsappname__="dreamweaver"</script><script src="http://use.edgefonts.net/aguafina-script:n4:default.js" type="text/javascript"></script>
</head>

	 <body>
			
		<div id="title">
			  <h3 align="center">File Upload</h3>
			</div>
  <form action="result.php" method="post" name="fileID" target="_self" class="inp" AUTOCOMPLETE="ON">       
 <h1>
 
<!--Input file name-->

	<label for="fileID">File Name: </label>
              <input type="text" name='file1' id='sampleID' list="samp"> </input><br>
             <datalist id="samp">
 
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">

	<head>
		<meta http-equiv="content-type" content="text/html;charset=utf-8" />
		<meta name="generator" content="Adobe GoLive" />
		<title>File Select</title>
					
	<!--The following script tag downloads a font from the Adobe Edge Web Fonts server for use within the web page. We recommend that you do not modify it.--><script>var __adobewebfontsappname__="dreamweaver"</script><script src="http://use.edgefonts.net/aguafina-script:n4:default.js" type="text/javascript"></script>
</head>

	 <body>
			
		<div id="title">
			  <h3 align="center">File Upload</h3>
			</div>
  <form action="result.php" method="post" name="fileID" target="_self" class="inp" AUTOCOMPLETE="ON">       
 <h1>
 
<!--Input file name-->

	<label for="fileID">File Name: </label>
              <input type="text" name='file1' id='sampleID' list="samp"> </input><br>
             <datalist id="samp">
 
<?php
$db = new mysqli('localhost', 'root', '', 'test_db');

$query = $db->query("SELECT * FROM  `file` ORDER BY  `file`.`name` ASC LIMIT 0 , 30");
while($row = $query->fetch_assoc()){
    $File_name = $row['name'];

echo "<option value=$File_name>$File_name/option> <br>";
}
?>
</datalist>

<input type="submit" class="button">
</form>
</body>
</html>

Edited August 18, 2014 by phpbnoobb23

Ch0cu3r · August 18, 2014

Is that the actual code for listing files? You do realise you have repeated the first 24 lines of HTML twice!

First sort your HTML out and make sure it is valid and then debug your PHP code to see why the files are not being listed.

phpbnoobb23 · August 18, 2014

Is that the actual code for listing files? You do realise you have repeated the first 24 lines of HTML twice!

First sort your HTML out and make sure it is valid and then debug your PHP code to see why the files are not being listed.

I edited my reply. The file names are being list. My error was that i added an (s) at the end of row, So it did not fetch the file names.

My real problem now is i'm still getting the "Invalid filename given" error when I press submit.

Edited August 18, 2014 by phpbnoobb23

Ch0cu3r · August 18, 2014

That is because you have named your File Name field file1

<input type="text" name='file1' id='sampleID' list="samp"> </input><br>

You need to the name it File_name as this is the name of field you are trying to get the value of in PHP ( $_POST['File_name'] )

Edited August 18, 2014 by Ch0cu3r

phpbnoobb23 · August 18, 2014

Thanks A lot guys. I appreciate all the help and input from everyone. Special thanks to Ch0cu3r for being extra patient. My problem is now solved.

Sign In

Query and output data from same table in db

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information