mac_gyver Posted January 22, 2017 Share Posted January 22, 2017 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted January 22, 2017 Share Posted January 22, 2017 Better eyes than me. I could barely make out the last line of that screenshot. Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 22, 2017 Author Share Posted January 22, 2017 Ha ha , good spot, going crossed eyes here. It worked thanks. onto the next step Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 22, 2017 Author Share Posted January 22, 2017 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; Quote Link to comment Share on other sites More sharing options...
Barand Posted January 22, 2017 Share Posted January 22, 2017 It's a one-off operation so slowness isn't a real issue. Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 22, 2017 Author Share Posted January 22, 2017 Ok, its into 30 mins now , so will hang on I guess. Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 22, 2017 Author Share Posted January 22, 2017 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted January 22, 2017 Share Posted January 22, 2017 If you haven't already, add an index to test_db on the whisky_id column. CREATE INDEX `idx_test_db_whisky_id` ON `test_db` (whisky_id); Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 22, 2017 Author Share Posted January 22, 2017 (edited) Hi , thank yes I have done that thanks. Now we have two tables I am struggling to pull the info down as only ever really worked with one in the past. Edited January 22, 2017 by Barny74 Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 22, 2017 Author Share Posted January 22, 2017 (edited) 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 January 22, 2017 by Barny74 Quote Link to comment Share on other sites More sharing options...
Barand Posted January 22, 2017 Share Posted January 22, 2017 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted January 22, 2017 Share Posted January 22, 2017 I can pull down info as per the image  - http://imgur.com/a/4vskU Is the average price the only thing you want to show as a result of the query on that page? Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 22, 2017 Author Share Posted January 22, 2017 I was hoping to show the avg price , last price / date sold. Then the rest on the next page , which at some point will be restricted to memebers.only Quote Link to comment Share on other sites More sharing options...
Barand Posted January 22, 2017 Share Posted January 22, 2017 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; Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 22, 2017 Author Share Posted January 22, 2017 (edited) 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 January 22, 2017 by Barny74 Quote Link to comment Share on other sites More sharing options...
Barand Posted January 22, 2017 Share Posted January 22, 2017 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; Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 22, 2017 Author Share Posted January 22, 2017 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>"; } ?>  Quote Link to comment Share on other sites More sharing options...
Barand Posted January 22, 2017 Share Posted January 22, 2017 url_img and whsky_id are not in the fields selected in the query. Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 22, 2017 Author Share Posted January 22, 2017 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. Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 22, 2017 Author Share Posted January 22, 2017 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>"; } } ?> Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 22, 2017 Author Share Posted January 22, 2017 Scrath that , it was the limit by 1 I had to remove. All sorted , thanks Quote Link to comment Share on other sites More sharing options...
Barand Posted January 22, 2017 Share Posted January 22, 2017 Â 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? Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 22, 2017 Author Share Posted January 22, 2017 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 Quote Link to comment Share on other sites More sharing options...
Barny74 Posted January 23, 2017 Author Share Posted January 23, 2017 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted January 23, 2017 Share Posted January 23, 2017 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. Â How do you create the csv at present? How many test_db records do you typically add at a time? How often do you have new whiskies? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.