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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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
Michael701Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.