Jump to content

Recommended Posts

i would look at row 55982 in your test_db table and see what length the name is (or just run a query to get the maximum length of the data in that column) and make the length of the whisky_name column longer than you expect the names to ever be.

  • Replies 54
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

With 50k+ recrods in my test_bd would the below take a while , seems to have said "loading" for a while now. I am guessing it is still working on it but just wanted an opinion on it. In case it has stalled , but not sure. I can clock on the "whisky" table , but when I click on the "test-db" is just has the yellow loading box in the middle.

UPDATE test_db t
    JOIN whisky w ON t.name=w.whisky_name
SET t.whisky_id = w.whisky_id;

Ok , thats run now and thank you so much. I have been runnig though the code to update it to the new names etc. I get the single listings on the main search which is great. When I click on the name it does not pull anything down from teh DB , I am a wee bit confused on which DB I am selecting from now. Code as below.

 

Search.php , which I think is ok now.

if (isset ($_POST['search'])) { //the 'search' refers to the 'search' name=search on the index page and makes does something when the search is pushed. 
		 $search = $_POST['search'];
		 $search = "%" . $search . "%"; // MySQL wildcard % either side of search to get partially matching results

									   //  No wildcard if you want results to match fully 
	} else {

	header ('location: index.php');

	}



	$stmt = $conn->prepare("SELECT whisky_name , whisky_id FROM whisky WHERE whisky_name LIKE :name ORDER BY whisky_name ASC"); // Use = instead of LIKE for full matching
	$stmt->bindParam(':name', $search);  
	$stmt->execute();
	$count = $stmt->rowCount(); // Added to count no. of results returned


	if ($count >= 1) { // Only displays results if $count is 1 or more

		echo "<div class='results_found'>";
		echo $count; 
		echo " results found<br>";
		echo "</div>";

	while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

	echo "<div class='results'>";
			echo "<div class='result_name'>";
					echo "<b>Whisky Name:</b><br>";
					echo "<a href='details1.php?id={$row['whisky_id']}' >{$row['whisky_name']}</a>";
					echo"<br>";
			echo "</div>";
	echo "</div>";
	} 

	} else {
	      echo " Sorry no records were found";
	}

	?>

details.php

<?php

	$page='details';
	include('header.php');
	include ('navbar.php'); 
	include ('connect.php');

	if (isset($_GET['id'])) {
    $sql = "SELECT price, url_img , lot_id, whisky_id , avprice FROM test_db JOIN (SELECT name, AVG(price) as avprice FROM test_db GROUP BY name) avcalc USING (name)
    WHERE whisky_id = :id";

    $stmt = $conn->prepare($sql);
    $stmt->execute( [ 'id' => $_GET['id'] ] );
    $row = $stmt->fetch();
    echo "<div class='details'>";
        echo "<br>";
        echo $row['name'];
        echo "<br><br>";
        echo "Average price based on all sales" ;
        echo"<br><br>";
        echo "<div class= 'price'>";
        echo " £";
        echo floor ($row ['avprice']);
        echo "</div>";
        echo "<br>";
        echo "<div class='img_border'>";
        echo "<img src='".$row ['url_img']."' /><br />";
        echo "</div>";
        echo "<br>";
        echo "<a href='records.php?id={$row['whisky_id']}' >Click here for more detialed results</a>";
        echo "<br>";
    echo "</div>";
}
	?>

records.php

<?php

	$page='details';
	include('header.php');
	include ('navbar.php'); 
	include ('connect.php');

	if (isset($_GET['id'])) {
    $sql = "SELECT whisky_id , date , name , price  FROM test_db WHERE whisky_id = :id ORDER BY date DESC";
   
    $stmt = $conn->prepare($sql);
    $stmt->execute( [ 'id' => $_GET['id'] ] );
    while( $row = $stmt->fetch() ) {
    echo "<div class='records'>";
        echo "<br>";
        echo $row['name'];
        echo "<br><br>";
        echo $row ['date'];
        echo"<br><br>";
        echo " £";
        echo floor ($row ['price']);
        echo "<br>";
        echo "<br>";
        echo "<br>";
    echo "</div>";
}
}
	?>


databases.

 

http://imgur.com/a/iwf1p

 

Thanks again

I can pull down info as per the image   - http://imgur.com/a/4vskU

 

using

details1.php

if (isset($_GET['id'])) {
    $sql = "SELECT whisky_id , price ,  avprice FROM test_db JOIN (SELECT whisky_id, AVG(price) as avprice FROM test_db GROUP BY whisky_id) avcalc USING (whisky_id)
    WHERE whisky_id = :id";

    $sql = "SELECT * FROM whisky WHERE whisky_id = :id";


Edited by Barny74

In records.php, for example, if you want the whisky name then you need to join the test_db table to the whisky table

SELECT whisky_id 
, date 
, whisky_name as name
, price  
FROM test_db
 INNER JOIN whisky USING (whisky_id) -- required to get the name
WHERE t.whisky_id = :id 
ORDER BY date DESC

Then

SELECT w.whisky_name
, price
, date
, avprice 
FROM test_db t
JOIN whisky w USING (whisky_id)
JOIN (
    SELECT whisky_id
    , AVG(price) as avprice 
    FROM test_db 
    GROUP BY whisky_id
    ) avcalc USING (whisky_id)
WHERE w.whisky_id = 1
ORDER BY date DESC 
LIMIT 1;

Ok thank you , I have update the code but now it just goes back to a page with no database content, I will keep the dates etc for later andif I can just get the name ,avg price that would be great , and I keep the code in for the dates last sold and price last sold so I can add that in later. really sorry about this and really appreciat the help

 

http://imgur.com/a/DPSr4

 

details1.php

<?php

	$page='details';
	include('header.php');
	include ('navbar.php'); 
	include ('connect.php');

	if (isset($_GET['id'])) {
    $sql = "SELECT w.whisky_name
, price
, date
, avprice 
FROM test_db t
JOIN whisky w USING (whisky_id)
JOIN (
    SELECT whisky_id,
    , AVG(price) as avprice 
    FROM test_db 
    GROUP BY whisky_id
    ) avcalc USING (whisky_id)
WHERE w.whisky_id = 1
ORDER BY date DESC 
LIMIT 1";


    $stmt = $conn->prepare($sql);
    $stmt->execute( [ 'id' => $_GET['id'] ] );
    $row = $stmt->fetch();
    echo "<div class='details'>";
        echo "<br>";
        echo $row['whisky_name'];
        echo "<br><br>";
        echo "Average price based on all sales" ;
        echo"<br><br>";
        echo "<div class= 'price'>";
        echo " £";
        echo floor ($row ['avprice']);
        echo "</div>";
        echo "<br>";
        echo "<div class='img_border'>";
        echo "<img src='".$row ['url_img']."' /><br />";
        echo "</div>";
        echo "<br>";
        echo "<a href='records.php?id={$row['whisky_id']}' >Click here for more detailed results</a>";
        echo "<br>";
    echo "</div>";
}
	?>


Edited by Barny74

You have left the id hard coded instead of " = :id ". This version below may also be more efficient

SELECT w.whisky_name
, price
, date
, avprice 
FROM test_db t
JOIN 
	whisky w USING (whisky_id)
JOIN (
    SELECT whisky_id
    , AVG(price) as avprice 
    FROM test_db 
    WHERE whisky_id = :id
    GROUP BY whisky_id
    ) avcalc USING (whisky_id)
ORDER BY date DESC 
LIMIT 1;

Ah ok, so much to take in I did not relaise that. Thanks. So do I need to also connect to the whisky table to pull the img down, also have an error on whisky_id , as per pic,

 

http://imgur.com/a/grgaZ

if (isset($_GET['id'])) {
    $sql = "SELECT w.whisky_name
, price
, date
, avprice 
FROM test_db t
JOIN 
    whisky w USING (whisky_id)
JOIN (
    SELECT whisky_id
    , AVG(price) as avprice 
    FROM test_db 
    WHERE whisky_id = :id
    GROUP BY whisky_id
    ) avcalc USING (whisky_id)
ORDER BY date DESC 
LIMIT 1";

    $stmt = $conn->prepare($sql);
    $stmt->execute( [ 'id' => $_GET['id'] ] );
    $row = $stmt->fetch();
    echo "<div class='details'>";
        echo "<br>";
        echo $row['whisky_name'];
        echo "<br><br>";
        echo "Average price based on all sales" ;
        echo"<br><br>";
        echo "<div class= 'price'>";
        echo " £";
        echo floor ($row ['avprice']);
        echo "</div>";
        echo "<br>";
        echo "<div class='img_border'>";
        echo "<img src='".$row ['url_img']."' /><br />";
        echo "</div>";
        echo "<br>";
        echo "<a href='records.php?id={$row['whisky_id']}' >Click here for more detailed results</a>";
        echo "<br>";
    echo "</div>";
}
	?>



 

AH ok. I was a bit confused as i was querying test_db and there is not url_img or whsky_id in this db. But I have added them and thats all good now.

 

So once agian thanks so much and I really appreciate you taking the time to assit me with this.

 

I will work on the final page now and hopefully work it out now I have a good base to work from, but If it is ok I will ask if I have any problems

 

Thnanks again.

Ha ha , back again...I know I need to add a WHILE loop to get this going , but how would I get all results showing for the specific whisky. Just not sure on the WHILE loop. Only one result showing at the moment.

 

http://imgur.com/a/qilCS

if (isset($_GET['id'])) {

        $sql = "SELECT w.whisky_name
        , price
        , date
        , avprice 
        ,url_img 
        ,whisky_id
        FROM test_db t
        JOIN 
            whisky w USING (whisky_id)
        JOIN (
            SELECT whisky_id
            , AVG(price) as avprice 
            FROM test_db 
            WHERE whisky_id = :id
            GROUP BY whisky_id
            ) avcalc USING (whisky_id)
        ORDER BY date DESC 
        LIMIT 1";
           
    $stmt = $conn->prepare($sql);
    $stmt->execute( [ 'id' => $_GET['id'] ] );
    while( $row = $stmt->fetch() ) {
    echo "<div class='records'>";
        echo "<br>";
        echo $row['whisky_name'];
        echo "<br><br>";
        echo $row ['date'];
        echo"<br><br>";
        echo " £";
        echo floor ($row ['price']);
        echo "<br>";
        echo "<br>";
        echo "<br>";
    echo "</div>";
}
}
	?>

 

I was hoping to show the avg price , last price / date sold

That is what that query was designed to do - just the latest record with price and date plus the average price.

 

When you list all the results for a whisky, what do you want to show?

Hi. I have it sorted I think. I am showing all the results of that whisky showing name , date , and all past prices. I just need ot work out how to convet the date to ddmmyy when I

echo $row ['date'];

as it currntly shows uo as yyyymmdd

Forgot i had already asked this in a different question. Sorry.

 

I do have a query. When i have a completly new whisky to add into the DB or add in a new auction sale , Do I do it via CSV or myphpadmin ?and if i did would i need to run the code that you kindly sent me to earlier. I.e populatimg the whisky table with the unique name (#3) and then #4 and #5

Rerunning #3, #4, #5 is a definite no-no. You could end up with completely different set of ids. That was, as previously stated, a one-off exercise.

 

  1. How do you create the csv at present?
  2. How many test_db records do you typically add at a time?
  3. How often do you have new whiskies?
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.