Using php and mysql switch from URL data and user input search

I have pay per click adds and I send people to a page

http://mysite.com/search-members-by-city.php?city=somecityname

Right now I have it set up so people can type in the city they want to search

$get_pro_sql = "SELECT DATE_FORMAT(profile_update,  '%b %e %Y at %r') aS fmt_profile_update, ID, username FROM DB where city='$city_menu_choice' ORDER BY update DESC";
$get_pro_res = mysqli_query($mysqli, $get_profiles_sql) or die(mysqli_error($mysqli));

I would like to make it so when someone clicks on an add and goes to this page, the members from "somecity" are posted.

Then if they want to search a different city they can type the name of the city in

<table width="250px">
      <form method='post' action=<?php $_SERVER['PHP_SELF'] ?> >
      <td><strong>City:</strong></td>
    <td>
      <input type="text" name="city_choice" size="20" value=<?php $_POST['city_choice'] ?> >
      </td>
      <td>
      <input type="submit" name="submit" id="submit"/>
      </td>
      </tr>
      </form>
      </table>

Can you help me so I can do this either or situation?

thanks

derekstattinAsked:
Who is Participating?
 
Michael701Connect With a Mentor Commented:
this right here wipes out the _POST value

$city_menu_choice='';
$city_menu_choice = isset($_POST['city_choice'])? mysqli_real_escape_string($_POST['city_choice']): '';
$city_menu_choice = isset($_GET['city'])? mysqli_real_escape_string($_GET['city']): '';

anything wrong with the code I posted?

$city_menu_choice='';
if (isset($_GET['city']))
  $city_menu_choice=$_GET['city'];
if (isset($_POST['city_choice']))
  $city_menu_choice=$_POST['city_choice'];

if you want to use real escape the manual states syntax as
string mysqli_real_escape_string ( resource link, string escapestr )
so you'll have to do this, RIGHT after the previous if statements.
$city_menu_choice=mysqli_real_escape_string ($mysqli, $city_menu_choice);

0
 
Michael701Commented:
like this?

$city_menu_choice='';
if (isset($_GET['city']))
  $city_menu_choice=$_GET['city'];
if (isset($_POST['city_choice']))
  $city_menu_choice=$_POST['city_choice'];

Open in new window

0
 
derekstattinAuthor Commented:
I put the code you sent above, above the sql statement,

$get_pro_sql = "SELECT DATE_FORMAT(profile_update,  '%b %e %Y at %r') aS fmt_profile_update, ID, username FROM DB where city='$city_menu_choice' ORDER BY update DESC";
$get_pro_res = mysqli_query($mysqli, $get_profiles_sql) or die(mysqli_error($mysqli));

but I am not having any luck,

can you give me another tip
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Michael701Commented:
can I point out the obvious?

$get_pro_sql  is not the same as $get_profiles_sql

:)

Michael

ps: been there done that before myself.
0
 
derekstattinAuthor Commented:
I have it right in my script, I just abreviated to make the question shorter, I just did not change the third profiles. I must be doing something else wrong. If I search , say philadelphia I still see chicago members.
0
 
Michael701Commented:
// after you set the sql

echo "get:".$_GET['city']."<br />\n";
echo "post:".$_POST['city_choice']."<br />\n";
echo "sql:".$get_profiles_sql."<br />\n";

Let's see if it's getting set correctly
0
 
derekstattinAuthor Commented:
$get_pro_sql = "SELECT DATE_FORMAT(profile_update,  '%b %e %Y at %r') aS fmt_profile_update, ID, username FROM DB where city='$city_menu_choice' ORDER BY update DESC";
$get_pro_res = mysqli_query($mysqli, $get_profiles_sql) or die(mysqli_error($mysqli));

I put your code above after, the above $sql

I don't get any variables echoed in the body of the page
0
 
derekstattinAuthor Commented:
Do you need more of the srcipt?
0
 
Michael701Commented:
even if you 'view source' on the html?

guess You'll have to post your php cope
0
 
derekstattinAuthor Commented:
<?php
$mysqli = mysqli_connect("");
$city_menu_choice = $_POST['city_choice'];
$city_menu_choice='';
if (isset($_GET['city']))
  $city_menu_choice=$_GET['city'];
if (isset($_POST['city_choice']))
  $city_menu_choice = $_POST['city_choice'];

//gather the topics
$get_pro_sql = "SELECT DATE_FORMAT(profile_update,  '%b %e %Y at %r') aS fmt_profile_update, ID, username, profile_update, city, state, profession FROM profile where city_menu_choice='$city' ORDER BY profile_update DESC";
$get_profiles_res = mysqli_query($mysqli, $get_profiles_sql) or die(mysqli_error($mysqli));

echo "get:".$_GET['city']."<br />\n";
echo "post:".$_POST['city_choice']."<br />\n";
echo "sql:".$get_profiles_sql."<br />\n";


if (mysqli_num_rows($get_profiles_res) < 1) {
      //there are no topics, so say so
      $display_block = "<p><strong><em>There are no member profiles for ".$city_menu_choice." at this time.</em></strong></p>";
} else {
      //create the display string
      $display_block = "
      <table cellpadding=\"10\" cellspacing=\"1\" border=\"0\" border-color=\"121212\">
      <tr>
      <th>User Name</th>
      <th>Location</th>
      <th>Profession</th>
      <th>View Profile</th>
      </tr>";

      while ($user_info = mysqli_fetch_array($get_profiles_res)) {
            
            $users_state_choice = $_POST['city_choice'];
        $state = stripslashes($user_info['state']);
            $city = stripslashes($user_info['city']);
              $username = stripslashes($user_info['username']);
            $profession =      stripslashes($user_info['profession']);
            $ID = stripslashes($user_info['ID']);
            $profile_update = stripslashes($user_info['profile_update']);

            //add to display
            $display_block .= "
            <tr>
            <td align=center>".$username."</td>
            <td align=center>".$city.",&nbsp;".$state."</td>
            <td align=center>".$profession."</td>
            <td><a href=\"show_members_profile.php?ID=".$ID."\">go to profile</a></td>
            </tr>";
      
      }
      //free results
      mysqli_free_result($get_profiles_res);
      
      //close connection to mysqli
      mysqli_close($mysqli);

      //close up the table
      $display_block .= "</table>";
}
?>
 <table width="250px">
      <form method='post' action=<?php $_SERVER['PHP_SELF'] ?> >
      <td><strong>City:</strong></td>
    <td>
      <input type="text" name="city_choice" size="20" value=<?php $_POST['city_choice'] ?> >
      </td>
      <td>
      <input type="submit" name="submit" id="submit"/>
      </td>
      </tr>
      </form>
      </table>
      <p>
      <?php echo $display_block; ?>
      </p>
0
 
dr_dedoCommented:
guess you need to favor get over post, but you are doing the reverse here, try this

<?php
$mysqli = mysqli_connect("");
$city_menu_choice='';
$city_menu_choice = isset($_POST['city'])? mysqli_real_escape_string($_POST['city']): '';
$city_menu_choice = isset($_GET['city'])? mysqli_real_escape_string($_GET['city']): '';
$get_pro_sql = "SELECT DATE_FORMAT(profile_update,  '%b %e %Y at %r') AS fmt_profile_update, ID, username, profile_update, city, state, profession FROM profile where city_menu_choice='$city_menu_choice' ORDER BY profile_update DESC";
$get_profiles_res = mysqli_query($mysqli, $get_profiles_sql) or die(mysqli_error($mysqli)."in\n$get_pro_sql");
 
...........
..........
.........

Open in new window

0
 
Michael701Commented:
change this
$get_pro_sql = "SELECT DATE_FORMAT(profile_update,  '%b %e %Y at %r') aS fmt_profile_update, ID, username, profile_update, city, state, profession FROM profile where city_menu_choice='$city' ORDER BY profile_update DESC";

to

$get_profiles_sql = "SELECT DATE_FORMAT(profile_update,  '%b %e %Y at %r') aS fmt_profile_update, ID, username, profile_update, city, state, profession FROM profile where city_menu_choice='$city' ORDER BY profile_update DESC";

0
 
derekstattinAuthor Commented:
Not working quite yet, do you have another suggestion? I still get only the ?city=somecity from the url, and the post does not work.
0
 
Michael701Commented:
few corrections to html, but shouldn't be the problem

what's the output from the echo commands? Is this available on the web? If so, post the url.

 
// px only valid in css
<table width="250">
// missing " around action value
      <form method='post' action="<?php $_SERVER['PHP_SELF'] ?>" >
      <td><strong>City:</strong></td>
    <td>
// missing " around value
      <input type="text" name="city_choice" size="20" value="<?php $_POST['city_choice'] ?>" >

Open in new window

0
 
derekstattinAuthor Commented:
no luck with that change
0
 
Michael701Commented:
what's the output from the echo commands? Is this available on the web? If so, post the url.
0
 
derekstattinAuthor Commented:
the url is http://real-estate-proforma.com/search-members-by-city.php?...eg ...city=chicago
I added the code below in the body, but it does not echo anything

<?php
echo "get:".$_GET['city']."<br />\n";
echo "post:".$_POST['city_choice']."<br />\n";
echo "sql:".$get_profiles_sql."<br />\n";
?>
0
 
Michael701Commented:
put the echo's right after the

$get_profiles_res = mysqli_query........

you also didn't add the quotes around the action and value= in your form (add the echo also)

action="<?php echo $_SERVER['PHP_SELF'] ?>" >

(view the html source code to verify)


0
 
derekstattinAuthor Commented:
I get this warning
Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given in /home/content/M/a/n/Manzanillo8/html/search-members-by-city.php on line 5
get:
post:chicago

Here is the code, I am sorry I am such an amatuer, thanks so much for your patients

sql:SELECT DATE_FORMAT(profile_update, '%b %e %Y at %r') aS fmt_profile_update, ID, username, profile_update, city, state, profession FROM profile where city='' ORDER BY profile_update DESC

Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given in /home/content/M/a/n/Manzanillo8/html/search-members-by-city.php on line 5
get:
post:chicago
sql:SELECT DATE_FORMAT(profile_update, '%b %e %Y at %r') aS fmt_profile_update, ID, username, profile_update, city, state, profession FROM profile where city='' ORDER BY profile_update DESC



<?php
$mysqli = mysqli_connect("");
$username = $_COOKIE["username"];
$city_menu_choice='';
$city_menu_choice = isset($_POST['city_choice'])? mysqli_real_escape_string($_POST['city_choice']): '';
$city_menu_choice = isset($_GET['city'])? mysqli_real_escape_string($_GET['city']): '';


$get_profiles_sql = "SELECT DATE_FORMAT(profile_update,  '%b %e %Y at %r') aS fmt_profile_update, ID, username, profile_update, city, state, profession FROM profile where city='$city_menu_choice' ORDER BY profile_update DESC";
$get_profiles_res = mysqli_query($mysqli, $get_profiles_sql) or die(mysqli_error($mysqli)."in\n$get_profiles_sql");
 


echo "get:".$_GET['city']."<br />\n";
echo "post:".$_POST['city_choice']."<br />\n";
echo "sql:".$get_profiles_sql."<br />\n";

 
if (mysqli_num_rows($get_profiles_res) < 1) {
      //there are no topics, so say so
      $display_block = "<p><strong><em>There are no member profiles for ".$city_menu_choice." at this time.</em></strong></p>";
} else {
      //create the display string
      $display_block = "
      <table cellpadding=\"10\" cellspacing=\"1\" border=\"0\" border-color=\"121212\">
      <tr>
      <th>User Name</th>
      <th>Location</th>
      <th>Profession</th>
      <th>View Profile</th>
      </tr>";

      while ($user_info = mysqli_fetch_array($get_profiles_res)) {
            
            $state = stripslashes($user_info['state']);
            $city_member = stripslashes($user_info['city']);
              $username = stripslashes($user_info['username']);
            $profession = stripslashes($user_info['profession']);
            $ID = stripslashes($user_info['ID']);
            $profile_update = stripslashes($user_info['profile_update']);

            //add to display
            $display_block .= "
            <tr>
            <td align=center>".$username."</td>
            <td align=center>".$city_member.",&nbsp;".$state."</td>
            <td align=center>".$profession."</td>
            <td><a href=\"show_members_profile.php?ID=".$ID."\">go to profile</a></td>
            </tr>";
            }
      //free results
      mysqli_free_result($get_profiles_res);
      //close connection to mysqli
      mysqli_close($mysqli);

      //close up the table
      $display_block .= "</table>";
}
?>
 <table width="250">

      <form method='post' action="<?php echo $_SERVER['PHP_SELF'] ?>" >

      <td><strong>City:</strong></td>
    <td>
      <input type="text" name="city_choice" size="20" value="<?php $_POST['city_choice'] ?>" >
      </td>
      <td>
      <input type="submit" name="submit" id="submit"/>
      </td>
      </tr>
      </form>
      </table>
0
 
derekstattinAuthor Commented:
thanks so much,

works great now!

I hope I can get better at php and mysql!

Thank you for spending so much time with me
0
All Courses

From novice to tech pro — start learning today.