Jump to content

Query and output data from same table in db


phpbnoobb23
Go to solution Solved by Ch0cu3r,

Recommended Posts

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."

 

Link to comment
Share on other sites

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 by phpbnoobb23
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by phpbnoobb23
Link to comment
Share on other sites

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 by adam_bray
Link to comment
Share on other sites

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>
Link to comment
Share on other sites

 

 

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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();
?> 
Link to comment
Share on other sites

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  ==

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by phpbnoobb23
Link to comment
Share on other sites

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 by phpbnoobb23
Link to comment
Share on other sites

  • Solution

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 by Ch0cu3r
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.