add a check or cross in mysql db

[ianhaney]

Hello

I am building a web based system for a customer of mine and they have asked if they can have a field called indexed and they want to be able to check a box if a site is indexed or leave it blank if it is not indexed or put a Y or N in the field and then they want to display a green check or red cross if the site is indexed or not on viewing the record

hope that makes sense and is possible

Ian

[styxlawyer]

Yes, it's possible. This link (https://www.google.co.uk/search?q=green+tick+red+cross+graphics&tbm=isch&tbo=u&source=univ&sa=X&ved=0ahUKEwiAjPabx9_PAhVJBsAKHX6gDpUQsAQIHQ) might help you find what you are looking for.

[ianhaney]

I have been having a go and came up with the following code but it only displays the one record and no others where as it should display 4



echo "<td><img src=images/" . $row['indexed'] == 'Yes' ? 'green-check.png' : 'red-cross.png' . "></td>";


below is the whole code I have



<?php
// connect to the database
include('dbfilename.php');

// get the records from the database
if ($result = $mysqli->query("SELECT website_id, category, url, DATE_FORMAT(domain_expiry_date, '%d/%m/%Y') AS domain_expiry_date, site_login_url, site_username_login, site_password_login, no_of_posts, da, tf, ct, domain_name_owner, DATE_FORMAT(domain_owner_dob, '%d/%m/%Y') AS domain_owner_dob, domain_owner_address, domain_owner_email, domain_owner_phone, email_account, email_account_url, email_account_address, email_username, email_password, registrar, registrar_url, registrar_username, registrar_password, hosting_company, hosting_url, hosting_username, hosting_password, DATE_FORMAT(hosting_expiry_date, '%d/%m/%Y') AS hosting_expiry_date, hosting_cpanel_url, hosting_cpanel_username, hosting_cpanel_password, sites_linked_out_to, DATE_FORMAT(last_post_date, '%d/%m/%Y') AS last_post_date, indexed FROM websites ORDER BY website_id"))
{
// display records if there are records to display
if ($result->num_rows > 0)
{
// display records in a table
echo "<table class='records'>";

// set table headers
echo "<tr>
<th>Website ID</th>
<th>Category</th>
<th>URL</th>
<th>DA</th>
<th>TF</th>
<th>CT</th>
<th>No Of Posts</th>
<th>Last Post Date</th>
<th>Indexed</th>
<th colspan='2'>Actions</th>
</tr>";

while ($row = $result->fetch_object())
{
// set up a row for each record
echo "<tr>";
echo "<td><a href='view-specific-website-data.php?id=" . $row->website_id . "'>".$row->website_id . "</a></td>";
echo "<td>" . $row->category . "</td>";
echo "<td><a href='http://" . $row->url . "' target='_blank'>" . $row->url . "</a></td>";
echo "<td>" . $row->da . "</td>";
echo "<td>" . $row->tf . "</td>";
echo "<td>" . $row->ct . "</td>";
echo "<td>" . $row->no_of_posts . "</td>";
echo "<td>" . $row->last_post_date . "</td>";

echo "<td><img src=images/" . $row['indexed'] == 'Yes' ? 'green-check.png' : 'red-cross.png' . "></td>";

echo "<td><a href='add-update-website-data.php?website_id=" . $row->website_id . "'>Edit</a></td>";
echo "<td><a href='delete.php?website_id=" . $row->website_id . "'>Delete</a></td>";
echo "</tr>";
}

echo "</table>";
}
// if there are no records in the database, display an alert message
else
{
echo "No results to display!";
}
}
// show an error if there is an issue with the database query
else
{
echo "Error: " . $mysqli->error;
}

// close database connection
$mysqli->close();

?>


At least I am not getting any errors, any help or point me in the right direction would be really appreciated

[DyDr]

You are getting a fatal run-time error at - $row['indexed']. You are fetching the data as an object. When you reference that as an array, it's an error. You need to use object notation to reference the indexed property. You are either getting the php error in the 'view source' of the page in your browser or you don't have php's error_reporting/display_errors settings set to report and display all the errors php detects.

Note: to concatenate a ternary expression inside a string, you must surround it with () so that it will be evaluated without including the concatenated parts of the string as part of the expression.

Edit: you are also missing quotes around url in the src attribute - src='image_url_here'

[ianhaney]

Hi

I have altered the coding and got the error disappeared but is snot displaying the images and part of the source code is displayed on the page

below is what I have now coding wise



echo "<td><img src='images/" . $row->indexed == 'Yes' ? 'green-check.png' : 'red-cross.png' . "'></td>";


on the page, is the following

red-cross.png'>red-cross.png'>red-cross.png'>red-cross.png'>

[DyDr]

That's probably due to this -


Note: to concatenate a ternary expression inside a string, you must surround it with () so that it will be evaluated without including the concatenated parts of the string as part of the expression.

[styxlawyer]

Clearly, your concatenation in that line isn't working. Try taking the ternary operator out and insert an extra line with an "if" statement and an intermediate variable instead.

[ianhaney]

So my fault missed that bit and got it working now

[ianhaney]

I have this code now, seems to be working correctly now



echo "<td><img src='images/" . (($row->indexed == 'Yes') ? 'green-check.png' : 'red-cross.png') . "'></td>";

[ianhaney]

Sorry I got stuck with the images being displayed on my paginated page, I have the following but all it does is display the red cross image for all the records but one should have the green tick



// echo out the contents of each row into a table
echo "<tr>";
echo "<td><a href='view-specific-website-data.php?website_id=" . $row[0] . "'>".$row[0] . '</a></td>';
echo '<td>' . $row[1] . '</td>';
echo '<td><a href="http://' . $row[2] . '" target="_blank">' . $row[2] . '</a></td>';
echo '<td>' . $row[8] . '</td>';
echo '<td>' . $row[9] . '</td>';
echo '<td>' . $row[10] . '</td>';
echo '<td>' . $row[7] . '</td>';
echo '<td>' . $row[34] . '</td>';
echo "<td><img src='images/" . (($row[35] == 'Yes') ? 'green-check.png' : 'red-cross.png') . "' style='width: 30px; height: 30px;'></td>";
echo '<td><a href="add-update-website-data.php?website_id=' . $row[0] . '">Edit</a></td>';
echo '<td><a href="delete.php?website_id=' . $row[0] . '">Delete</a></td>';
echo "</tr>";

[DyDr]

$row[35]

Is that the correct column, and how do you know it is? Why aren't you using an associative or object fetch mode so that you would know that it is the correct column, regardless of what your query is SELECTing or how your table columns are organized?


'Yes'

Is that the correct value, with that exact capitalization?

[ianhaney]

I counted the rows in phpmyadmin and is row 35 and the correct column, I am guessing it would be better to alter the following line

FROM


$row = $result->fetch_row();


TO



while ($row = $result->fetch_object())
{


then echo each row like the following method instead of $row[35] and so on



echo "<td><img src='images/" . (($row->indexed == 'Yes') ? 'green-check.png' : 'red-cross.png') . "' style='width: 30px; height: 30px;'></td>";


I can confirm the 'Yes' is correct in the mysql with the capitalization

[ianhaney]

This is the error I am getting, it says on line 124 when I put error reporting in

Notice: Undefined offset: 35 in

Notice: Undefined offset: 35 in

Notice: Undefined offset: 35 in

Notice: Undefined offset: 35 in

line 124 is below



echo "<td><img src='images/" . (($row[35] == 'Yes') ? 'green-check.png' : 'red-cross.png') . "' style='width: 30px; height: 30px;'></td>";

[DyDr]

That's because it's not 35. Numerical array indexing starts at zero, which is why your code using $row[0] for the website_id works.

[ianhaney]

Sorry have sorted the issue and made the coding easier and simpler and gone against how it was with $row[35] etc, I have done it like the following



<?php
// connect to the database
include('dbfile.php');

$per_page=5;
if (isset($_GET["page"])) {

$page = $_GET["page"];

}

else {

$page=1;

}

// Page will start from 0 and Multiple by Per Page
$start_from = ($page-1) * $per_page;

//Selecting the data from table but with limit
$query = "SELECT removed column names FROM websites ORDER BY website_id LIMIT $start_from, $per_page";
$result = mysqli_query ($mysqli, $query);

//Now select all from table
$query = "select * from websites";
$result = mysqli_query($mysqli, $query);

// Count the total records
$total_records = mysqli_num_rows($result);

//Using ceil function to divide the total records on per page
$total_pages = ceil($total_records / $per_page);

//Going to first page
echo "<center><a href='../home.php'>Back to Main Dashboard</a> | <a href='view-all-websites.php'>View All Websites</a> | <a href='view-paginated-websites-data.php?page=1'>".'First Page'."</a> ";

for ($i=1; $i<=$total_pages; $i++) {

echo "<a href='view-paginated-websites-data.php?page=".$i."'>".$i."</a> ";
};
// Going to last page
echo "<a href='view-paginated-websites-data.php?page=$total_pages'>".'Last Page'."</a></center> ";

// display data in table
echo "<table class='records'>";
echo "<tr>
<th>Website ID</th>
<th>Category</th>
<th>URL</th>
<th>DA</th>
<th>TF</th>
<th>CT</th>
<th>No Of Posts</th>
<th>Last Post Date</th>
<th>Indexed</th>
<th colspan='2'>Actions</th>
</tr>";

while ($row = mysqli_fetch_assoc($result)) {
// echo out the contents of each row into a table
echo "<tr>";
echo "<td><a href='view-specific-website-data.php?website_id=" . $row['website_id'] . "'>".$row['website_id'] . '</a></td>';
echo "<td>" . $row['category'] . "</td>";
echo "<td><a href='http://" . $row['url'] . "' target='_blank'>" . $row['url'] . "</a></td>";
echo "<td>" . $row['da'] . "</td>";
echo "<td>" . $row['tf'] . "</td>";
echo "<td>" . $row['ct'] . "</td>";
echo "<td>" . $row['no_of_posts'] . "</td>";
echo "<td>" . $row['last_post_date'] . "</td>";
echo "<td><img src='images/" . (($row['indexed'] == 'Yes') ? 'green-check.png' : 'red-cross.png') . "' style='width: 30px; height: 30px;'></td>";

echo "<td><a href='add-update-website-data.php?website_id=" . $row['website_id'] . "'>Edit</a></td>";
echo "<td><a href='delete.php?website_id=" . $row['website_id']. "'>Delete</a></td>";

echo "</tr>";
}

// close table>
echo "</table>";

// close database connection
$mysqli->close();

?>


is working perfect now