Displaying linked members from Mysql

[mhallmon]

Hello! I'm trying to create a page on my site that will allow paid members to search for other members using check boxes and multi-select drop down boxes to select what type of members to display. For instance; male or female.

 

I dont know where to start. I am new to mysql and kinda new to php as well. I use "connect_to_mysql.php" to connect to the database. The database name is "members". I would like to include "sex", "age", "ethnicity","weight" , and "state".

 

After the paid member selects the fields they are looking for and hits submit... I need it to take them to a page that displays a link (their name and picture) to the profiles of all the members that meet their reqs .

 

How would I go about making this page and making it work? I have searched the internet for tutorials and have came up with nothing that helps... or at least that I can understand.

 

I am sort of running on a short time line and really need some major help.

 

Please help me.

[gizmola]

This isn't how we do things here.  Read the php manual, read some tutorials, google, buy a book on Amazon.  When you have some code, we can help you.  What you're talking about doing is as basic as can be, but it's driven by an understanding of your own database structure, and SQL.  Do you even have a simple SQL query you've tested out in phpMyAdmin?

[mhallmon]

What I was asking for was someone to point me in the right direction. I bought the PHP and MySql Bible from Wiley books... a great $50 investment and it's helped me with my site so far. I have watched most of the tutorials offered by Adam from web intersect... I've searched the net for tutorials but have not came up with anything to help me. Like I said... I am new to PHP and MySql. I've tried to google but no matter how I word it... the results are never what I am looking for or they don't work. I'm not looking for someone to do it for me... just point me in the direction I need to be in. I want to learn how to do it... that way in the future I wont have issues with it.

[mhallmon]

I didn't include the code earlier... maybe I should have. This is what I have:

 

<?php

include_once "connect_to_mysql.php";

 

mysql_select_db("membership", $con);

 

$result = mysql_query("SELECT * FROM members

WHERE sex='male'");

 

while($row = mysql_fetch_array($result))

  {

  echo $row['firstname'] . " " . $row['lastname'];

  echo "<br />";

  }

?>

 

The code works perfect but I need it to do more than just return the information. I need this to be searchable using check- boxes and drop downs. 

[xyph]

You're new to PHP/MySQL. Don't you think a comprehensive search with many variables is a little complex?

 

I think you first want to understand how the MySQL engine searches, and how to optimize searches so your complicated queries don't bog down if a couple users feel like going on a clicking-spree.

 

You're off to a good start, though.

[mhallmon]

I understand that something like this would be complex for me being that I am new to both PHP and MySql. That's the reason I came here for advise. I have found, watched and read tutorials on how to optimize my database. This all just started as a hobby for learning experience but turned into an overnight blowup. That's why I am asking for advise on how to go about implementing the search aspects. Any suggestions on tutorials, videos or straight out advice would be nice. 

[mhallmon]

I have been using the code posted prior by creating another page and just linking it... but that's kind of annoying and the members can only search by my criteria. My basic code is below as you can see none of it is linked and I can't figure out how to make each one a link to the members profile:

 

 

 

<?php

include_once "connect_to_mysql.php";

 

mysql_select_db("membership", $con);

 

$result = mysql_query("SELECT * FROM members");

 

echo "<table border='2'>

<tr>

<th>Firstname</th>

<th>Lastname</th>

<th>Sex</th>

<th>Age</th>

<th>Weight</th>

<th>Height</th>

<th>Location</th>

</tr>";

 

while($row = mysql_fetch_array($result))

  {

  echo "<tr>";

  echo "<td>" . $row['firstname'] . "</td>";

  echo "<td>" . $row['lastname'] . "</td>";

  echo "<td>" . $row['sex'] . "</td>";

  echo "<td>" . $row['age'] . "</td>";

  echo "<td>" . $row['weight'] . "</td>";

  echo "<td>" . $row['height'] . "</td>";

  echo "<td>" . $row['state'] . "</td>";

  echo "</tr>";

  }

echo "</table>";

 

mysql_close($con);

?>

 

 

 

[gizmola]

You need to break things into individual pieces.

 

First - you need a script that takes a parameter (the member_id)  from a url and queries for that individual member, then displays the information in an html page.

 

You will get the param inside the script from the $_GET[] superglobal array.

Let's assume that script is named showmember.php --

 

Then a call to get member with member_id 5 would be:

 

showmember.php?id=5

 

Inside your showmember.php script at the top:

 

$id = (int)$_GET['id'];

if ($id > 0) {
  $query = 'SELECT * from members WHERE member_id = $id";
  // do your mysql query, fetch, display data if found else show not found message
}

 

Now all you need to do on your list form is to have an anchor tag that creates the link to showmember.php?id= with the appropriate member_id you get in your result for the row, in the same way you are displaying other information.  You might want to make the anchor around the firstname and lastname columns or you could just have a link in a column that says:  "See Profile". 

 

For your search form, start with an html form that provides all the options you would want available in the form. 

 

You would add to your script a similar section at the top that gets the contents of the submitted form from the $_POST superglobal.

 

Based on the existence and contents of elements in your form, you will want to create the query criteria, by adding elements to the WHERE clause.

 

For example if your form has a state drop down, then you would include the state portion.

 

Hopefully this gets you moving in the right direction.

[mhallmon]

Okay. So this is what I came up with... I used pagenation. It all looks a little generic but I guess I can tweek it later. My issue now is that I can't get the pic to show up... it just shows as a broken image:

 

<?php
     //Include the PS_Pagination class
       include('ps_pagination.php');

        //Connect to mysql db
        $conn = mysql_connect("localhost", "******", "*******");
        mysql_select_db('membership',$conn);
        $sql = "SELECT * FROM members";// WHERE niche = '{$_GET['niche']}'";
        //Create a PS_Pagination object
        $pager = new PS_Pagination($conn,$sql,10,20);
        //The paginate() function returns a mysql
        //result set for the current page
        $rs = $pager->paginate();
        //Loop through the result set
        while($row = mysql_fetch_assoc($rs)) {

//   $a=0;
//   while ($a < $num) {

//   $id=mysql_result($result,$a,"id");
//   $title=mysql_result($result,$a,"title");
  //  $strategies=mysql_result($result,$a,"strategies");
//   $client=mysql_result($result,$a,"client");
//   $copy=mysql_result($result,$a,"copy");
//   $thumbmedia=mysql_result($result,$a,"thumbmedia");
//   $niche=mysql_result($result,$a,"niche");
    $id=$row['id'];
    $firstname=$row['firstname'];
    $lastname=$row['lastname'];
    $sex=$row['sex'];
    $age=$row['age'];
    $height=$row['height'];
    $weight=$row['weight'];
$state=$row['state'];
$files = "memberFiles/$id/pic1.jpg";



echo "<table border='2'>
<tr>
<th>Image</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Sex</th>
<th>Age</th>
<th>Weight</th>
<th>Height</th>
<th>Location</th>

</tr>";

 {
  echo "<tr>";
  echo '<div class="column3"><img src="memberFiles/"$id"/pic1.jpg" alt="Ad" width="100%" /></div></div>';
  echo "<td>".$files."</td>";
  echo "<td>" . $row['firstname'] . "</td>";
  echo "<td>" . $row['lastname'] . "</td>";
  echo "<td>" . $row['sex'] . "</td>";
  echo "<td>" . $row['age'] . "</td>";
  echo "<td>" . $row['weight'] . "</td>";
  echo "<td>" . $row['height'] . "</td>";
  echo "<td>" . $row['state'] . "</td>";
  
  echo "</tr>";
  }
  
echo "</table>";


  //  $a++;
  //  }

        }
        //Display the navigation
        echo $pager->renderNav();
    ?>

 

 

 

Why wont this show the image?

 

echo '<div class="column3"><img src="memberFiles/"$id"/pic1.jpg" alt="Ad" width="100%" /></div></div>'; 

 

 

[gizmola]

If you look at the url you are outputing you'll not that it literally contains the variable names, rather than the contents of the variables at runtime. When you use single quotes you get that behavior, as single quotes create a "string constant".  What you want is either a string concatenated together in pieces or to use double quotes so that you get variable interpolation.  In this case, all the double quotes are a problem but there are various ways around this.  One technique you can use is to drop out of php where the interpreter then considers your code to be html.

 

//your script
?>
" alt="Ad" width="100%" />

 

You might also find it useful to use a "heredoc" but I'll leave that to you to lookup if you're interested.  You can also escape all the internal double quotes and still have that work, but I personally find that tedious, error prone and hard to maintain when you have html with a lot of double quotes around attributes.