simple (i think) php/msql

Beginners - Learning PHP
#1

Hi Guys,

could use a little help with a script im trying to write… It’s not outputting the way I want it to, and I’m not sure where I’m going wrong. My php/mysql skills are not that good, It’s all new to me.

I’m pretty sure i’ve set the database up correctly, however i think there’s something wrong in the while loop in my script. Can anyone help? or point out anything else that looks wrong too?

It’s supposed to display a link to the pdf that is related to the keyword. I’ve had it working in the past where it displays the actual pdf, but i need it to list hyperlinks instead as its more practical if there are multiple results.

Sorry If i’m not making sense…

<?php include 'connect.php'; $result = mysqli_query($con,"SELECT * FROM pdfs WHERE Keywords LIKE '%stu%'"); $kword = $row['Keyword']; $count = 1; echo ("Your search results for ".$kword. "are below"); while($row = mysqli_fetch_array($result)) { $pdf=$row['FILENAME']; echo ("<a href='http://www.mysite.co.uk/$pdf</a>"); } mysqli_close($con); ?>
#2
#3

Hi Scott,

I have a field in my database called Keywords.
the ‘count’ line was going to count the result number, so in the results listing I could display it as

1 . http://www.mysite.co.uk/pdfs/pdf1.pdf

2 . http://www.mysite.co.uk/pdfs/pdf2.pdf

etc. although i hadn’t finished it I didnt think it would do any harm while i was working on the rest.

I tried using your modified script, and it does echo the hyperlink, put only to the first of the 2 pdf’s it should pick up… and it keeps repeating the ‘don’t forget a title’ echo… which is also hyperlinked to the pdf. It’s stuck in some kind of loop and not going on to the second pdf.

#4

Hi

Yes it will keep echoing out don’t forget a title as this is static… I wasn’t sure what you wanted there. Ideally, you’ll have the title in the database?

Try this:

[PHP]

<?php include 'connect.php'; $result = mysqli_query($con,"SELECT * FROM pdfs WHERE Keywords LIKE '%stu%'"); $rows = mysqli_fetch_array($result); $count = 1; //Don't know if I haven't got the complete code but you don't appear to be doing anything with this echo ("Your search results for " . $rows['Keyword'] . " are below"); foreach ($rows AS $row) { $count++; echo ("" . $row['title'] . ""); } mysqli_close($con); ?>

[/PHP]

If it doesn’t work, send me the whole code and a dump of the table/s you’re using.

[EDIT]

I’d also use

[PHP]
echo “Your search results for " . $_POST[‘keyword’] . " are below”;
[/PHP]

Instead of:

[PHP]
echo (“Your search results for " . $rows[‘Keyword’] . " are below”);
[/PHP]

#5

Hi,

Thanks again scott. I notice you switched it to a foreach loop. just covered them yesterday in my php/msql night class… and I did think maybe I should redo it using a foreach… but the examples I read online show a while…

I got a syntax error on line 12 it says… more than likely something I’ve done…
I’ll post my code/database but no laughing!

[php]<?php
include ‘connect.php’;
$result = mysqli_query($con,“SELECT * FROM pdfs
WHERE Keywords LIKE ‘%stu%’”);
$rows = mysqli_fetch_array($result);

$count = 1; //Don't know if I haven't got the complete code but you don't appear to be doing anything with this
echo ("Your search results for " . $rows['Keywords'] . " are below");

foreach ($rows AS $row)
{
$count++;
echo ("<a href=‘http://www.mysite.co.uk/" . $row[‘FILENAME’] . $count"’>" . $row[‘title’] . “”);
}
mysqli_close($con);
?>
[/php]

[php]<?php
// Create connection
$con=mysqli_connect(“xxx”,“xxx”,“xxx”,“xxx”);

// Check connection
if (mysqli_connect_errno())
{
echo “Failed to connect to MySQL:” . mysqli_connect_error();
exit();
}
?>[/php]

[code]-- phpMyAdmin SQL Dump
– version 4.0.8
– http://www.phpmyadmin.net

– Host: localhost
– Generation Time: Mar 12, 2014 at 10:06 PM
– Server version: 5.1.72-cll
– PHP Version: 5.3.17

SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;
SET time_zone = “+00:00”;

–
– Database: bictech_pdfdb

–
– Table structure for table pdfs

CREATE TABLE IF NOT EXISTS pdfs (
ID int(11) NOT NULL AUTO_INCREMENT,
FILENAME varchar(100) NOT NULL,
THUMBNAIL varchar(100) NOT NULL,
Keywords varchar(500) NOT NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=‘PDF file search database’ AUTO_INCREMENT=3 ;

–
– Dumping data for table pdfs

INSERT INTO pdfs (ID, FILENAME, THUMBNAIL, Keywords) VALUES
(1, ‘newsite/pdfs/test1.pdf’, ‘’, ‘stu test1’),
(2, ‘newsite/pdfs/test2.pdf’, ‘’, ‘stu test2’);
[/code]

#6

It was suggested that I really need 3 tables for what I want to do… although linking the 3 is beyond me at the moment, and I can’t see why it’s not possible doing it the way I’m trying at the moment… although I’m sure if I have maybe 20-40 keywords assigned to a pdf and there’s 100 pdfs maybe things will get slow? I don’t know…

#7

Sorry for the slow reply, I finished work.

[PHP]
$result = mysqli_query($con,“SELECT * FROM pdfs
WHERE Keywords LIKE ‘%stu%’”);

  echo "<h1>Showing results for </h1>"; //You would post whatever the user is searching here
  $count = 0;
  while($row = $result->fetch_assoc()){
    $count++;
    echo "Record $count: <a href='http://www.yoursite.co.uk/" . $row['FILENAME'] . "'>" . $row['FILENAME'] . "</a><br />";
  }

[/PHP]

Tested using your dump and is working.

Just to note, you’ve posted the username/password for your database. Unless you’re working on a local environment, I’d edit it and remove it.

[EDIT TO ADD]

With you saying night school and needing extra tables, I’m assuming you’re talking about normalization? Without seeing your brief and understanding the requirements, it’s difficult to tell. What I see from your dump is a simple user upload of PDF files with accompanying thumbnail images. How I envision that working is:

Users
id
userid
username
password
saltkey (optional)
active (optional)
timestamp

PendingUsers (optional)
id
userid

Sessions (optional)
id
userid
timestampin
timestampout

Uploads
id
pdf
thumb
timestamp
userid
approved (optional)

Cron (optional but brownie points)
id
uploadid

So a user registers and PendingUsers is updated with the userid. This is for the user to verify their email address. Once verified (check on login) user is logged in. They then upload a PDF file which updates the Cron table as mentioned, this is optional. What I was thinking here is UX, rather than use ImageMagick when the user uploads (takes time), it sits in the cron table and you set up a cron job to strip out the first page of the PDF and convert it to an image. As the PDF uploads, it has a default pending value of 0 meaning an admin can review it before it goes live.

#8

I can’t find the edit button? perhaps i should just change the log in details anyway lol.

This script i’m trying to get working isnt part of my night class… Something i’m trying to sort for a friend that he’s not been able to use for over a year…

wants a search box on his website that will search through pdfs and display them based on the keyword a viewer types in. I couldnt see a way of reliably reading the pdfs, someone also told me it might get a bit too much/heavy for the server and suggested assigning keywords to each pdf in a database.

I’ll have a look at the code shortly and get back to you :slight_smile: thanks a bunch for your help so far.

#9

can i ask a moderator to remove the username name and password on this post? i don’t think i can edit my early posts…

#10

I’ve just reported the post to the mods. I’d change the details now anyway.

#11

I have removed the login info.

#12

Thanks Guys :slight_smile:

I’ve altered the code to display a title instead of the filename… I figured its more ‘viewer/reader’ friendly to add a descriptive title instead.

[php] $result = mysqli_query($con,“SELECT * FROM pdfs
WHERE Keywords LIKE ‘%stu%’”);

    echo "<h1>Showing results for </h1>" . $row['Keywords']; //You would post whatever the user is searching here
    $count = 0;
    while($row = $result->fetch_assoc()){
      $count++;
      echo "$count. <a href='http://www.yoursite.co.uk/" . $row['FILENAME'] . "'>" . $row['Title'] . "</a><br />";
   }[/php]

I don’t suppose there’s an easy way to echo a jpg thumbnail if I add them?

also, is there a way or returning a message if there are no results? With an If statement I could put an else… can I do something similar with a while statement like above?

I’ve not yet come across the fetch_assoc()) you used… I might ask the lecturer to quickly go over it next class…

Quite made up with the help I’ve had here… used another forum before this php**eaks… and they don’t half make you feel unwelcome…

#13

Am I doing something stupidly wrong here when trying to echo my description field? Tried a few different ways with nothing appearing or errors.

[php] $keywordinput = $_POST[“keywordinput”];
$result = mysqli_query($con,“SELECT * FROM pdfs
WHERE Keywords LIKE ‘%$keywordinput%’”);

    echo "<h1>Showing results for " . $keywordinput . "</h1>"; //You would post whatever the user is searching here
    $count = 0;
    while($row = $result->fetch_assoc()){
      $count++;
      echo "$count." . $row['THUMBNAIL'] . "<a href='http://www.bictechnology.co.uk/" . $row['FILENAME'] . "'>" . $row['Title'] . "</a><br />";
	  echo $row['description'];
   }[/php]
#14

Hi,

have altered my ‘while’ state to the following
[php] while($row = $result->fetch_assoc()){
$count++;
echo “$count.” . $row[‘THUMBNAIL’] . “<a href='http://www.bictechnology.co.uk/” . $row[‘FILENAME’] . “’>” . $row[‘Title’] . “
”;
echo" " . $row[‘description’];
}[/php]

and I get the following output

Showing results for
1.test1 pdf
2.Test2 PDF
3.newsite/images/thumbs/2503.jpgX-ray/Gamma Dosemeters
PERSONAL DOSIMETER Easy to operate, low cost pocket microprocessor instrument with high accuracy and reliability.

#15

You haven’t said whether this update is actually working?

Also, there’s a way for you to get the thumbnail automatically from the PDF when the user uploads it. It’s called ImageMagick and it’s relatively straightforward to use.

#16

Hi again,

I’m pretty happy with it now to be honest, just playing with the output formatting trying to get it right.
At the moment there’s no way of adding the pdfs other than manually in phpmyadmin.

I suppose thats next thing I’ll be working on. :slight_smile:

the only thing i need to figure out really now is getting it to display a message when there’s no results instead of showing nothing.

I’ll take a look at that ImageMagik too :slight_smile:

Thanks for all your time and help.

#17

OK, I can’t get the ‘No results’ message to work…

Am i miles off ?

[php] $keywordinput = $_POST[“keywordinput”];
$result = mysqli_query($con,"SELECT * FROM pdfs

    WHERE Keywords LIKE '%$keywordinput%'");

    echo "<h1>Showing results for " . $keywordinput . "</h1>"; //You would post whatever the user is searching here
    $count = 0;
	
		if ($result=="") {
			echo ("No matches");
						}
			 else {

        while($row = $result->fetch_assoc()){
	      $count++;
    	  echo "$count." . $row['THUMBNAIL'] . "<a href='http://www.bictechnology.co.uk/" . $row['FILENAME'] . "'>" . $row['Title'] . "</a><br />" . $row['description'] . "<br /><br />";
		}
   }[/php]
#18

Have tried the following too, and it keeps giving me ‘no matches’ even when there should be…

[php] if (!$result)
{
echo (“No matches”);
}
else {

        	while($row = $result->fetch_assoc()){
	      $count++;
    	  echo "$count." . $row['THUMBNAIL'] . "<a href='http://www.bictechnology.co.uk/" . $row['FILENAME'] . "'>" . $row['Title'] . "</a><br />" . $row['description'] . "<br /><br />";
		}[/php]
#19

Try and print out the query after you execute it:

[PHP]
echo “

”.print_r($result)."
";
[/PHP]

That will show you the exact query it is using.

#20

Your code is not right. Try this:

[php]<?php
$hostdb = ‘localhost’;
$dbname = ‘YOURDATABASENAME’;
$username = ‘root’;
$password = ‘’;

$table =‘YOURTABLENAME’;

try
{
$pdo = new PDO(“mysql:host=localhost;dbname=$dbname”, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql  = "SELECT * FROM $table";
$stmt = $pdo->prepare($sql);
$stmt->execute();

$result = $stmt->fetchAll();

if (count($result))
    {
    foreach ($result as $row)
        {
        echo '<pre>';
        print_r($row);
        echo '</pre>';
        }
    }
else
    {
    echo "No rows returned.";
    }
}

catch (PDOException $e)
{
echo 'ERROR: ’ . $e->getMessage();
}
?>[/php]

Sponsor our Newsletter | Privacy Policy | Terms of Service