Plugin for Database Query and Table Display on Frontend

alskik, any luck with your search? I am looking for a similar solution.

Cheers

Hi Manik Insomniac,

I did find a solution, although it wasn’t a straightforward one.

I had to install the plugin ‘PHPCode Snippets’ and write a script manually to retrieve the information from tables on the database. Once I had written the script (with included styling where necessary), I placed the shortcode on the page. Example of the script below;

function curPageURL() {
$pageURL = ‘http’;
if ($_SERVER[“HTTPS”] == “on”) {$pageURL .= “s”;}
$pageURL .= “://”;
if ($_SERVER[“SERVER_PORT”] != “80”) {
$pageURL .= $_SERVER[“SERVER_NAME”].”:”.$_SERVER[“SERVER_PORT”].$_SERVER[“REQUEST_URI”];
} else {
$pageURL .= $_SERVER[“SERVER_NAME”].$_SERVER[“REQUEST_URI”];
}
return $pageURL;
}
echo ‘<form method=”post” action=”‘.curPageURL().'”><table><tr><td></td><td>First Name</td><td>Surname</td></tr><tr>’;
try{
$dbh = new PDO(‘mysql:host=HOST;dbname=DBNAME’,’DBNAME’,‘PASSWORD’);
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$sth = $dbh->query(“SELECT members_hisname, members_hername FROM members GROUP BY members_hisname, members_hername”);
echo ‘<td></td><td><select name=”fname”><option value=””>All</option>’;
while($member= $sth->fetch(PDO::FETCH_OBJ)){
if($member->members_hisname !=”){ echo “<option value=’$member->members_hisname’>$member->members_hisname</option>”; }
if($member->members_hername !=”){ echo “<option value=’$member->members_hername’>$member->members_hername</option>”; }
}
echo ‘</select></td>’;

//Surname
$sth = $dbh->query(“SELECT DISTINCT(members_surname) FROM members WHERE members_surname != ” ORDER BY members_surname ASC”);
echo ‘<td><select name=”sname”><option value=””>All</option>’;
while($member= $sth->fetch(PDO::FETCH_OBJ)){
echo “<option value=’$member->members_surname’>$member->members_surname</option>”;
}
echo ‘</select></td></tr><tr><td>Year</td><td>Make</td><td>Model</td><td>Reg</td></tr>’;

//Year
$sth = $dbh->query(“SELECT DISTINCT(cars_year) FROM cars WHERE cars_year != ” ORDER BY cars_year ASC”);
echo ‘<tr><td><select name=”cyear”><option value=””>All</option>’;
while($car= $sth->fetch(PDO::FETCH_OBJ)){
echo “<option value=’$car->cars_year’>$car->cars_year</option>”;
}
echo ‘</select></td>’;

//Make
$sth = $dbh->query(“SELECT DISTINCT(cars_make) FROM cars WHERE cars_make != ” ORDER BY cars_make ASC”);
echo ‘<td><select name=”cmake”><option value=””>All</option>’;
while($car= $sth->fetch(PDO::FETCH_OBJ)){
echo “<option value=’$car->cars_make’>$car->cars_make</option>”;
}
echo ‘</select></td>’;

//Model
$sth = $dbh->query(“SELECT DISTINCT(cars_model) FROM cars WHERE cars_model != ” ORDER BY cars_model ASC”);
echo ‘<td><select name=”cmodel”><option value=””>All</option>’;
while($car= $sth->fetch(PDO::FETCH_OBJ)){
echo “<option value=’$car->cars_model’>$car->cars_model</option>”;
}
echo ‘</select></td>’;

//Reg
$sth = $dbh->query(“SELECT cars_reg FROM cars WHERE cars_reg != ” ORDER BY cars_reg ASC”);
echo ‘<td><select name=”creg”><option value=””>All</option>’;
while($car= $sth->fetch(PDO::FETCH_OBJ)){
echo “<option value=’$car->cars_reg’>$car->cars_reg</option>”;
}
echo ‘</select></td></tr>’;

echo ‘<tr><td colspan=”12″ style=”text-align:center”><input type=”submit” value=”View Members” /></td></tr></table>
</form>’;

if(isset($_POST[‘fname’]) || isset($_POST[‘sname’]) || isset($_POST[‘cyear’]) || isset($_POST[‘cmake’]) || isset($_POST[‘cmodel’]) || isset($_POST[‘creg’]) )
{
//RESULTS
if($_POST[‘fname’] !=”) { $sql.= ‘ AND (m.members_hisname=:fname OR m.members_hername=:fname)’;}
if($_POST[‘sname’] !=”) { $sql.= ‘ AND m.members_surname=:sname’;}
if($_POST[‘cyear’] !=”) { $sql.= ‘ AND c.cars_year=:cyear’;}
if($_POST[‘cmake’] !=”) { $sql.= ‘ AND c.cars_make=:cmake’;}
if($_POST[‘cmodel’] !=”) { $sql.= ‘ AND c.cars_model=:cmodel’;}
if($_POST[‘creg’] !=”) { $sql.= ‘ AND c.cars_reg=:creg’;}
if($sql == ”){$sql = ‘ AND 1=1’;}
$full_sql = “SELECT DISTINCT(m.members_number), m.members_hisname, m.members_hername,m.members_phone, m.members_mobile, m.members_mobile2, m.members_fax, m.members_email, m.members_email2, m.members_website, m.members_surname, m.members_postcode, m.members_address1, m.members_address2, m.members_address3, m.members_address4 FROM members m LEFT JOIN cars c ON (m.members_number = c.cars_member) WHERE 1=1 $sql AND m.members_status != -1 ORDER BY m.members_surname ASC”;
$sth = $dbh->prepare($full_sql);

if($_POST[‘fname’] !=”) { $sth->bindvalue(“fname”,$_POST[‘fname’]);}
if($_POST[‘sname’] !=”) { $sth->bindvalue(“sname”,$_POST[‘sname’]);}
if($_POST[‘cyear’] !=”) { $sth->bindvalue(“cyear”,$_POST[‘cyear’]);}
if($_POST[‘cmake’] !=”) { $sth->bindvalue(“cmake”,$_POST[‘cmake’]);}
if($_POST[‘cmodel’] !=”) { $sth->bindvalue(“cmodel”,$_POST[‘cmodel’]);}
if($_POST[‘creg’] !=”) { $sth->bindvalue(“creg”,$_POST[‘creg’]);}
$sth->execute();
echo ‘<table>’;
while($result= $sth->fetch(PDO::FETCH_OBJ)){
echo” <tr><td colspan=’12’ style=’border-left:1px solid black;border-top:1px solid black;border-right:1px solid black’> </td></tr>
<tr>
<td style=’border-left:1px solid black’>Name </td><td>$result->members_hisname $result->members_surname</td>
<td>Phone</td><td style=’border-right:1px solid black’>$result->members_phone</td>
</tr>
<tr>
<td style=’border-left:1px solid black’>&</td><td>$result->members_hername</td>
<td>Mobile</td><td style=’border-right:1px solid black’>$result->members_mobile</td>
</tr>
<tr>
<td style=’border-left:1px solid black’></td><td></td>
<td>Mobile 2</td><td style=’border-right:1px solid black’>$result->members_mobile2</td>
</tr>
<tr>
<td style=’border-left:1px solid black’></td><td></td>
<td>Fax</td><td style=’border-right:1px solid black’>$result->members_fax</td>
</tr>
<tr>
<td style=’border-left:1px solid black’></td><td></td>
<td>Email</td><td style=’border-right:1px solid black’>$result->members_email</td>
</tr>
<tr>
<td style=’border-left:1px solid black’>Address</td><td>$result->members_address1</td>
<td>Email 2</td><td style=’border-right:1px solid black’>$result->members_email2</td>
</tr>
<tr>
<td style=’border-left:1px solid black’></td><td>$result->members_address2</td>
<td>Website</td><td style=’border-right:1px solid black’>$result->members_website</td>
</tr>
<tr>
<td style=’border-left:1px solid black’></td><td>$result->members_address3</td>
<td></td><td style=’border-right:1px solid black’></td>
</tr>
<tr>
<td style=’border-left:1px solid black’></td><td>$result->members_address4</td>
<td></td><td style=’border-right:1px solid black’></td>
</tr>
<tr>
<td style=’border-left:1px solid black’>Postcode</td><td>$result->members_postcode</td>
<td></td><td style=’border-right:1px solid black’></td>
</tr>
<tr><td colspan=’12’ style=’border-left:1px solid black;border-right:1px solid black’> </td></tr>”;

$sql=”;
if($_POST[‘cyear’] !=”) { $sql.= ‘ AND cars_year=:cyear’;}
if($_POST[‘cmake’] !=”) { $sql.= ‘ AND cars_make=:cmake’;}
if($_POST[‘cmodel’] !=”) { $sql.= ‘ AND cars_model=:cmodel’;}
if($_POST[‘creg’] !=”) { $sql.= ‘ AND cars_reg=:creg’;}
if($sql == ”){$sql = ‘ AND 1=1’;}
$full_sql = “SELECT * FROM cars WHERE cars_member=:id $sql”;
$sth2 = $dbh->prepare($full_sql);

if($_POST[‘cyear’] !=”) { $sth2->bindvalue(“cyear”,$_POST[‘cyear’]);}
if($_POST[‘cmake’] !=”) { $sth2->bindvalue(“cmake”,$_POST[‘cmake’]);}
if($_POST[‘cmodel’] !=”) { $sth2->bindvalue(“cmodel”,$_POST[‘cmodel’]);}
if($_POST[‘creg’] !=”) { $sth2->bindvalue(“creg”,$_POST[‘creg’]);}
$sth2->bindvalue(“id”,$result->members_number);
$sth2->execute();
if($sth2->rowCount() > 0){
echo “<tr><td colspan=’12’ style=’border-left:1px solid black;border-right:1px solid black’>Cars</td></tr>
<tr><td style=’border-left:1px solid black’>Year</td><td>Reg</td><td>Car</td><td style=’border-right:1px solid black’>Model</td></tr>”;
}
while($car= $sth2->fetch(PDO::FETCH_OBJ)){
echo “<tr><td style=’border-left:1px solid black’>$car->cars_year</td><td>$car->cars_reg</td><td>$car->cars_make</td><td style=’border-right:1px solid black’>$car->cars_model</td></tr>”;
}
echo “<tr><td colspan=’12’ style=’border-left:1px solid black;border-right:1px solid black;border-bottom:1px solid black’> </td></tr>”;
}
echo ‘</table>’;
}

} catch(PDOException $e) {
echo $e->getMessage();
}

From what I gathered, this was the most straightforward way of doing it.

Feel free to hack/rework the above code to produce the desired result on your site, assuming you can work your way around the coding a bit.

Good luck,
AlskiK