Retrieving download link from a MySQL database

gazfocus · December 18, 2008

I have a MySQL database and one of the fields needs to contain the file path for a download link, however when my page retrieves the record, because the filename has spaces in it, it only retrieves upto the first space (the filepath is ../files/mp3/0000 - Jubilee Liverpool.mp3 so it retrieves ../files/mp3/0000). Is there any way round this?

The field type if VARCHAR

Thanks

corbin · December 18, 2008

What does your query look like and your coding for spitting out the data? MySQL doesn't stop at spaces.

gazfocus · December 18, 2008

The code for uploading the files is...

<html>
<head>
<SCRIPT TYPE="text/javascript">
</SCRIPT>
<link href="/joomla/templates/at_flexmen/css/template_css.css" rel="stylesheet" type="text/css">
</head>
<body>

<?php

//////////////////////////////////////////
//// MySQL Database Connection ///////////
//////////////////////////////////////////
$host = "xxxxx";
$user = "xxxxx";
$db_name= "xxxxx";
$pass= "xxxxx";

$conn = mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($db_name, $conn) or die(mysql_error());

if ($_POST[viewed] != "yes")
{
echo "<form name=\"form1\" method=\"post\" enctype=\"multipart/form-data\" action=\"$SERVER[php_SELF]\">
<fieldset class=\"input\">
  <table width=\"200\" border=\"0\">
    <tr>
      <td valign=\"top\"><input name=\"title\" type=\"text\" id=\"title\" tabindex=\"1\" class=\"inputbox\" style=\"font-size:12px\" value=\"Sermon Title\" size=\"40\" maxlength=\"50\" onfocus=\"this.value = ( this.value == this.defaultValue ) ? '' : this.value;return true;\"></td>
    </tr>
    <tr>
      <td valign=\"top\"><input name=\"speaker\" type=\"text\" id=\"speaker\" tabindex=\"2\" class=\"inputbox\" style=\"font-size:12px\" value=\"Speaker\" size=\"40\" maxlength=\"50\" onfocus=\"this.value = ( this.value == this.defaultValue ) ? '' : this.value;return true;\"></td>
    </tr>
    <tr>
      <td height=\"27\" valign=\"top\">
  <input name=\"dd\" type=\"text\" id=\"dd\" tabindex=\"3\" class=\"inputbox\" style=\"font-size:12px\" value=\"dd\" size=\"5\" maxlength=\"2\" onfocus=\"this.value = ( this.value == this.defaultValue ) ? '' : this.value;return true;\">
      <input name=\"mm\" type=\"text\" id=\"mm\" tabindex=\"4\" class=\"inputbox\" style=\"font-size:12px\" value=\"mm\" size=\"5\" maxlength=\"2\" onfocus=\"this.value = ( this.value == this.defaultValue ) ? '' : this.value;return true;\">
      <input name=\"yyyy\" type=\"text\" id=\"yyyy\" tabindex=\"5\" class=\"inputbox\" style=\"font-size:12px\" value=\"yyyy\" size=\"10\" maxlength=\"4\" onfocus=\"this.value = ( this.value == this.defaultValue ) ? '' : this.value;return true;\"></td>
    </tr>
    <tr>
      <td valign=\"top\"><input name=\"mp3\" type=\"text\" id=\"mp3\" tabindex=\"6\" class=\"inputbox\" style=\"font-size:12px\" value=\"MP3 Filename\" size=\"40\" maxlength=\"50\" onfocus=\"this.value = ( this.value == this.defaultValue ) ? '' : this.value;return true;\"></td>
    </tr>
<tr>
  <td><br /><p style=\"font-size:12px; color:#b53a04; background-color:#e3e8eb\"><b>Sermon Notes</b></p><input name=\"notes\" type=\"file\" id=\"notes\" tabindex=\"7\"></td>
    </tr>

<tr>
  <td valine=\"top\"><br /><input type=\"submit\" name=\"submit\" id=\"submit\" value=\"Upload\" class=\"button\" style=\"font-size:12px\" /> <input type=\"reset\" name=\"reset\" id=\"reset\" value=\"Reset\" class=\"button\" style=\"font-size:12px\" /><br /><br /><br /></td>
</tr>
<tr>
  <td><input name=\"viewed\" type=\"hidden\" id=\"viewed\" value=\"yes\"></td>
</tr>
</table>
</form>";
}
else
{

$uploadmp3 = '../files/mp3/';
$uploadnotes = '../files/notes/';

$title = $_POST[title];
$speaker = $_POST[speaker];
$sermonDate = $_POST[yyyy]."-".$_POST[mm]."-".$_POST[dd];
$mp3 = $_POST[mp3];
$notes = $_FILES[notes][name];
$dlmp3 = $uploadmp3.$mp3;
$dlnotes = $notes;

if (move_uploaded_file($_FILES[notes][tmp_name],$uploadnotes.$notes))
{
$sql="INSERT INTO kirkbysermons (title, speaker, date, mp3, notes, notesType, notesSize)
VALUES ('$title', '$speaker', '$sermonDate', '$dlmp3', '$dlnotes', '$notesType', '$notesSize')";

mysql_query($sql) or die(mysql_error());

echo "<p style=\"font-size:12px; color:#b53a04; background-color:#e3e8eb\"><b>".$title."</b></p><p style=\"font-size:12px\"> has been successfully uploaded.</p>";

} 
else 
{
    echo "Possible file upload attack!\n";
}
}
?>
</body>
</html>

The code for displaying the results is...

<html>
<head>
<SCRIPT TYPE="text/javascript">
</SCRIPT>
<link href="/joomla/templates/at_flexmen/css/template_css.css" rel="stylesheet" type="text/css">
</head>
<body>

<?php

//////////////////////////////////////////
//// MySQL Database Connection ///////////
//////////////////////////////////////////
$host = "xxxxx";
$user = "xxxxx";
$db_name= "xxxxx";
$pass= "xxxxx";

$conn = mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($db_name, $conn) or die(mysql_error());

$sql = "SELECT title, speaker, DAYNAME(date) as day, DATE_FORMAT(date,'%D %M %Y') as eDate , mp3, notes, notesType FROM kirkbysermons ORDER by date ASC";

if ($result = mysql_query($sql,$conn)) {
  if (mysql_num_rows($result)) {
    while ($newArray = mysql_fetch_array($result)) {
      $title = $newArray['title'];
      $speaker = $newArray['speaker'];
  $day = $newArray['day'];
      $date = $newArray['eDate'];
      $mp3 = $newArray['mp3'];
      $notes = $newArray['notes'];
      $notesType = $newArray['notesType'];

      echo $title . "<br />";
      echo $speaker . "<br />";
      echo $day . " " . $date . "<br />";
      echo "<a href=$mp3>MP3</a>" . "<br />";
  if ($notesType == "application/vnd.ms-powerpoint")
  {
      echo "<a href=$notes>Notes</a>" . "<br />";
  }
      echo "<br />";
    }
  }
}


?>
</body>
</html>

For some reason, the results for titles and names, etc works fine and if I go into phpMyAdmin and manually add a ' at the beginning and end of each url, it works ok but everytime I try to add a ' into the coding to do it automatically I get MySQL errors.

fenway · December 18, 2008

you need to escape the quote -- look at mysql_real_escape_string()

gazfocus · December 21, 2008

you need to escape the quote -- look at mysql_real_escape_string()

I've just tried this and it doesn't seem to work. Is there any way to put a ' in a variable or in a mysql table field?

ngreenwood6 · December 21, 2008

fenways suggestion should work are you sure that you are doing it correctly. Any variables that are posted should looke like this:

$title = mysql_real_escape_string($_POST['title']);

fenway · December 22, 2008

you need to escape the quote -- look at mysql_real_escape_string()

I've just tried this and it doesn't seem to work. Is there any way to put a ' in a variable or in a mysql table field?

Then you didn't do it correctly.

Sign In

Retrieving download link from a MySQL database

Recommended Posts

gazfocus

Link to comment

Share on other sites

corbin

Link to comment

Share on other sites

gazfocus

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

gazfocus

Link to comment

Share on other sites

ngreenwood6

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information