PHP - If .... Else Lookup In Mysql

Hi all,

I'm currently using Maxminds geoip and geoipcity databases to determine the users country but it struggles to determine if a visitor is from Northern Ireland (it just shows them as United Kingdom).
Has anyone got (any) GeoIP to accurately determine Northern Ireland visitors?

Thanks

Throughout the application I'm building, I include a db.php file which has the information about the local database so that I can use it wherever I do a database call. However, I am now using a second database located on a remote server in order to fetch additional information not stored on the local database, inside a while loop. This fail miserably and I have tested many things and need a bit of guidance.

Code simplified:
Code: [Select]
function grab_expiration_date ($number) {

    $remote_link = mysql_connect('remote_host', 'user', 'pass');
    $remote_connect = mysql_select_db('remote_db', $remote_link);
   
    $query = "SELECT ExpirationDate FROM remote_table WHERE Number='$number'";
    $result = mysql_query($query, $remote_link);
    $num_rows = mysql_num_rows($result);
   
    if ($num_rows == 1) {
       
        $r = mysql_fetch_assoc($result);
        $expire_date = $r['ExpirationDate'];
       
        mysql_close($remote_link);
       
        return $expire_date;
    } else {
       
        return false;
    }

}


$query = "SELECT Number FROM local_table;
$result = mysql_query($query, $local_link);

while ($r = mysql_fetch_assoc($result)) {

      $number = $r['Number'];
      $expire_date = fetch_expiration_date($number);

      if ($expire_date) {

          $query2 = UPDATE local_table SET ExpirationDate='$expire_date' WHERE Number='$number'";
          $result2 = mysql_query($query2, $link);

          if (!$result2) {
               echo 'Could not update the Expiration Number because: ' . mysql_error();
               exit();
          }

      } else {
          echo 'Nothing found, or dublicates';
      }
}

The attempt of update the local table fails, and mysql_error() does not even return anything, stating what's wrong. In the past, I fetched this information from a website using CURL, but since I now have access to the database, hitting the database is by far faster. Any help would be much appreciated.

Hello all,   For the UPDATE portion of my CRUD WebApp what I would like to do is to bring in (and display) the values (of a selected row) from my transaction table.   This is working just fine for all fields which are of the "input type". The problem I'm having is with two fields which are of the "select type" i.e. dropdown listboxes.   For those two fields, I would like to bring in all the valid choices from the respective lookup/master tables, but then have the default/selected value be shown based on what's in the transaction table. The way I have it right now, those two fields are showing (and updating the record with) the very first entry's in the two lookup tables/select query.   The attached picture might make things a little bit clearer. You'll notice in the top screenshot that the first row (which is the one I'm selecting to update) has a "Store Name" = "Super Store" and an "Item Description" = "Old Mill Bagels". Now, when I click the "update" botton and I'm taken to the update screen, the values for those two fields default to the very first entries in the SELECT resultset i.e. "Food Basics" and "BD Cheese Strings". Cricled in green (to the top-left of that screenshot) is the result of an echo that I performed, based on the values that are in the transaction record.   I cannot (for the life of me) figure out how to get those values to be used as default/selected values for the two dropdown's...so that if a user does not touch those two dropdown fields, the values in the transaction table will not be changed.   Your help will be greatly appreciated.   Here's a portion of the FORM code:  

            <form class="form-horizontal" action="update.php?idnumber=<?php echo $idnumber?>" method="post">
                <?php
                    // Connect to Store_Name (sn) table to get values for dropdown
                    $pdo = Database::connect();
                    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                    $sql = "SELECT DISTINCT store_name FROM store_master ORDER BY store_name ASC";
                    $q_sn = $pdo->prepare($sql);
                    $q_sn->execute();
                    $count_sn = $q_sn->rowCount();        
                    $result_sn = $q_sn->fetchAll();
                    Database::disconnect();

                    // Connect to Item_Description (id) table to get values for dropdown                    
                    $pdo = Database::connect();
                    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                    $sql = "SELECT DISTINCT product_name FROM product_master ORDER BY product_name ASC";
                    $q_id = $pdo->prepare($sql);
                    $q_id->execute();
                    $count_id = $q_id->rowCount();        
                    $result_id = $q_id->fetchAll();
                    Database::disconnect();

                    foreach($fields AS $field => $attr){
                        $current_store_name = $values['store_name'];
                        $current_item_description = $values['item_description'];
                        
                        //Print the form element for the field.
                        if ($field == 'store_name')
                            {
                                echo $current_store_name;
                                echo '<br />';
                                echo $current_item_description;
                            
                                echo '<div class="control-group">';
                                    echo "<label class='control-label'>{$attr['label']}: </label>";
                                    echo '<div class="controls">';                            
                                        //Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
                                        echo '<select class="store-name" name="store_name">';                                            
                                            // echo '<option value="">Please select...</option>';
                                            foreach($result_sn as $row)
                                                {
                                                    echo "<option value='" . $row['store_name'] . "'>{$row['store_name']}</option>";
                                                }
                                                // $row['store_name'] = $current_store_name;
                                        echo "</select>";

                                    echo '</div>';
                                echo '</div>';
                            }
                        elseif ($field == 'item_description')
                            {
                                echo '<div class="control-group">';
                                    echo "<label class='control-label'>{$attr['label']}: </label>";
                                    echo '<div class="controls">';                            
                                        echo '<select class="item-desc" name="item_description">';
                                            // echo '<option value="">Please select...</option>';
                                            foreach($result_id as $row)
                                                {
                                                    echo "<option alue='" . $row['product_name'] . "'>{$row['product_name']}</option>";
                                                }    
                                        echo "</select>";
                                    echo '</div>';
                                echo '</div>';                                        
                            }
                        else
                            {
                                echo '<div class="control-group">';
                                    echo "<label class='control-label'>{$attr['label']}: </label>";
                                    echo '<div class="controls">';                            
                                        //Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
                                        echo '<input type="text" name="'.$field.'"' . (isset($values[$field]) ? ' value="'.$values[$field].'"' : '') . ' /></label>';                            
                                    echo '</div>';
                                echo '</div>';                                        
                            }

    And here's some other declarations/code which I think might be required.  

    $fields = array(
        'store_name'         => array('label' => 'Store Name',
                                     'error' => 'Please enter a store name'),
        'item_description'     => array('label' => 'Item Description',
                                     'error' => 'Please enter an item description'),
        'qty_pkg'             => array('label' => 'Qty / Pkg',
                                     'error' => 'Please indicate whether it\'s Qty or Pkg'),
        'pkg_of'             => array('label' => 'Pkg. Of',
                                     'error' => 'Please enter the quantity'),
        'price'             => array('label' => 'Price',
                                     'error' => 'Please enter the price'),
        'flyer_page'         => array('label' => 'Flyer Page #',
                                     'error' => 'Please enter the flyer page #'),
        'limited_time_sale' => array('label' => 'Limited Time Sale',
                                     'error' => 'Please enter the days for limited-time-sale'),
        'nos_to_purchase'     => array('label' => 'No(s) to Purchase',
                                     'error' => 'Please enter the No. of items to purchase')
    );    
 
...
...
....
        {
            // If [submit] isn't clicked - and therfore POST array is empty - perform a SELECT query to bring in
            // existing values from the table and display, to allow for changes to be made
            $pdo = Database::connect();
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $sql = "SELECT * FROM shoplist where idnumber = ?";
            $q = $pdo->prepare($sql);
            $q->execute(array($idnumber));
            $values = $q->fetch(PDO::FETCH_ASSOC);

            if(!$values)
                {
                    $error = 'Invalid ID provided';
                }        
            Database::disconnect();
        }

    If there's anything I've missed please ask and I'll provide.   Thanks  

Hi, I've currently started to modify a chat script of mine to output a moderation panel but the moderation page seems empty(blank) every time I load it. What im trying to do is to take the ID part in my URL via the $_GET and look it up in my database table in the column named id, then select that specific row to be able to retrieve the StringyChat_ip and place it into another table to ban the IP and the second thing im trying to do is to be able to delete the specific row from my table.   My Http link look something like    http://imagecrab.fre.../ban.php?id=159   and my ban.php page where I want to lookup the 159 part and do the banning etc looks like

<?
include("admin_code_header.php");


if ($_POST["DeletePost"]) {
    $id = $_POST['id'];
    $query = "DELETE FROM ".$dbTable." WHERE id='".$id."'"; 
    mysql_query($query);
    echo "ID removed from system: ".$id;
  }


if ($_POST["BanIP"]) {
    $IP_To_Add = $_POST["ip"];
    
      $sql = "INSERT INTO ".$IPBanTable." (ip) VALUES (\"$IP_To_Add\")";
      $result = mysql_query($sql);
  }
$result = mysql_query("SELECT * FROM ".$dbTable." WHERE id='".$id."'",$db);

  while ($myrow = mysql_fetch_array($result)) {
    $msg = $myrow["StringyChat_message"];
$idm = $myrow["id"];



?>
<html>
<form name="form<? echo $myrow["id"];?>" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">

<input name="DeletePost" type="submit" id="DeletePost" value="Delete">

<input name="BanIP" type="submit" id="BanIP" value="Ban <? echo $myrow["StringyChat_ip"];?>">
    </form>
</html>
<?
}
?>

My login script stores the user's login name as $_SESSION[ 'name'] on login. For some unapparent reason, i'm getting errors stating that $user  and $priv are  undefined variables, though I've attempted to define $user as being equal to $_SESSION['name'], using $user to look up the the user's privilege level (stored as the su column ) in the SQL table, and then where the result of the sql query is $priv which is then evaluated in an if statement. I can't seem to figure out why this might not be working.

The code I'm using:

<?php
session_start();
function verify() {
	//verify that the user is logged in via the login page. Session_start has already been called. 
	if (!isset($_SESSION['loggedin'])) {
	header('Location: /index.html'); 
	exit;
	}
	//if user is logged in, we then lookup necessary privleges. $_SESSION['name'] was written with the login name upon login. Privleges   // are written in db as a single-digit integer of of 0 for users, 1 for administrators, and 2 for special users. 
        $user === $_SESSION['name'];
    //Connect to Databse
	$link = mysqli_connect("127.0.0.1", "database user", "password", "database");
	if (!$link) {
  	  echo "Error: Unable to connect to MySQL." . PHP_EOL;
    	echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    	echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    	exit;
        }
        //SQL Statement to lookup privlege information. 
       if ($result = mysqli_query($link, "SELECT su FROM accounts WHERE username = $user", MYSQLI_STORE_RESULT)) {
         //LOOP TO CYCLE THROUGH SQL RESULTS AND STORE Privlege information as vairable $priv.
            while ($row = $result->fetch_assoc()) {
           $priv === $row["su"];
            }
        }
        // close SQL connection.
        mysqli_close($link);

 // Verify privleges and take action. Only a privlege of "1" is allowed to view this page. A privlege of "2" indicates special 
//accounts used in other scripts that have certain indermediate additional functions, but are not trusted administrators. 

        if ($priv !== 1) {
        echo $_SESSION['name'];
        echo "you have privlege level of $priv";
        echo "<br>";
        echo 'Your account does not have the privleges necessary to view this page';
        exit;
        }
        
}
verify();
?>

 

create table mimi (mimiId int(11) not null, mimiBody varchar(255) );
<?php
//connecting to database
include_once ('conn.php');
$sql ="SELECT mimiId, mimiBody FROM mimi";

$result = mysqli_query($conn, $sql );

$mimi = mysqli_fetch_assoc($result);

$mimiId ='<span>No: '.$mimi['mimiId'].'</span>';

$mimiBody ='<p class="leading text-justify">'.$mimi['mimiBody'].'</p>';
?>
//what is next?

i want to download pdf or text document after clicking button or link how to do that

Hello everyone,

Sorry if this has been answered but if it has I can't find it anywhere. So, from the begining then.  Lets say I had a member table and in it I wanted to store what their top 3 interests are.  Their$ row has all the usual things to identify them userID and password etc.. and I had a further 3 columns which were labled top3_1 top3_2 & top3_3 to put each of their interests in from a post form.

If instead I wanted to store this data as a PHP Array instead (using 1 column instead of 3) is there a way to store it as readable data when you open the PHPmyadmin?

At the moment all it says is array and when I call it back to the browser (say on a page where they could review and update their interests) it displays 'a' as top3_01 'r' as top3_02 and 'r' as top3_03 (in each putting what would be 'array' as it appears in the table if there were 5 results. Does anyone know what I mean?

For example -

If we had a form which collected the top 3 interests to put in a table called users,

Code: [Select]
<form action="back_to_same_page_for_processing.php" method="post" enctype="multipart/form-data">
   
    <input name="top3_01" type="text" value="enter interest number 1 here" />
    <input name="top3_02" type="text" value="enter interest number 2 here" />
    <input name="top3_03" type="text" value="enter interest number 3 here" />
    <input type="submit" name="update_button" value=" Save and Update! " />

   
    </form> // If my quick code example for this form is not correct dont worry its not the point im getting at :)
And they put 'bowling' in top3_01, 'running' in top3_02 and 'diving' in top3_03 and we catch that on the same page with some PHP at the top -->

Code: [Select]
        if (isset($_POST)['update_button']) {
   
    $top3_01 = $_POST['top3_01']; // i.e, 'bowling' changing POST vars to local vars
    $top3_02 = $_POST['top3_02']; // i.e, 'running'
    $top3_03 = $_POST['top3_03']; // i.e, 'diving'

With me so far? If I had a table which had 3 columns (1 for each interest) I could put something like -

  Code: [Select]
   include('connect_msql.php');
   
    mysql_query("Select * FROM users WHERE id='$id' AND blah blah blah");
   
    mysql_query("UPDATE users SET top3_01='$top3_01', top3_02='$top3_02', top3_03='$top3_03' WHERE id='$id'");


And hopefully if ive got it right, it will put them each in their own little column. Easy enough huh? But heres the thing, I want to put all these into an array to be stored in the 1 column (say called 'top3') and whats more have them clearly readable in PHPmyadmin and editable from there yet still be able to be called back an rendered on page when requested.

Continuing the example then, assuming ive changed the table for the 'top3' column instead of individual colums,  I could put something like this -


Code: [Select]
            if (isset($_POST)['update_button']) {
       
        $top3_01 = $_POST['top3_01']; // i.e, 'bowling' changing POST vars to local vars
        $top3_02 = $_POST['top3_02']; // i.e, 'running'
        $top3_03 = $_POST['top3_03']; // i.e, 'diving'
   
    $top3_array = array($top3_01,$top3_02,$top3_03);
   
    include('connect_msql.php');
   
    mysql_query("UPDATE members SET top3='$top3_array' WHERE id='$id' AND blah blah blah");
But it will appear in the column as 'Array' and when its called for using a query it will render the literal string. a r r in each field instead.  Now I know you can use the 'serialize()' & 'unserialize()' funtcions but it makes the entry in the database practically unreadable. Is there a way to make it readable and editable without having to create a content management system?

If so please let me know and I'll be your friend forever, lol, ok maybe not but I'd really appreciate the help anyways.  The other thing is, If you can do this or something like it, how am I to add entries to that array to go back into the data base?

I hope ive explained myself enough here, but if not say so and I'll have another go.  Thanks very much people,  L-PLate (P.s if I sort this out on my own ill post it all here)

I have following piece of code below, and I would like to be able to express it pure SQL, something that could go into a .sql file :

$request_string='SELECT topic_forum_id FROM topic_table WHERE topic_id= 2014';
$query=database->prepare($request_string);
$query->execute();
$data=$query->fetch();
$query->closeCursor();
$forum=$data['topic_forum_id'];

$request_string='INSERT INTO post_table
(post_topic,post_forum) VALUES (2014,:forum)';
$query=database->prepare($request_string);
$query->bindValue(':forum',$forum,PDO::PARAM_INT);
$query->execute();
$data=$query->fetch();
$query->closeCursor();

  Is it possible ?

So i have this php as shown below. It should make a list of comments with comment replies below their comment respectively.

The problem is that it only goes through and shows 1 comment and all the comment replies for that one comment. It should be showing all comments i have in the db for that article.

If i remove the second while then it shows all the comments correctly but no comment replies then...

How do i get this script to loop through the db for every comment but also loop through every comment reply for that $row[id]?

If anyone has a better / more efficient way of what I am trying to do, please explain or show example (i am open to anything)...

Code: [Select]
// what article are we showing?
$article_to_show_id = $_GET['article_id'];


$active_is_set_text = "1"; // Active Column text that makes it okay to show

// Finding the article
$search_for_article = mysql_query("SELECT * FROM articles WHERE id = '$article_to_show_id' AND active = '$active_is_set_text'");

while($row = mysql_fetch_array($search_for_article))
{
// format the last updated date right
$update_date_edit = $row[update_date];

$update_date_edit = date('F j, Y \a\t h:ia', $update_date_edit);

$row[update_date] = $update_date_edit;



// format the submit updat date right
$submit_date_edit = $row[submit_date];

$submit_date_edit = date('F j, Y \a\t h:ia', $submit_date_edit);

$row[submit_date] = $submit_date_edit;


echo '
<div>
', $row[title] ,'
</div>


<div>
by: ', $row[author] ,' on ', $row[submit_date] ,'
</div>



<div>
', $row[content] ,'
</div>


<div>
Last Updated: ', $row[update_date] ,'
</div>

<form action="article_reply.php" method="post">
<input type="hidden" name="article_id" value="', $row[id] ,'" />
<button name="article_reply" type="submit" value="submit">Reply</button>
</form>

';

}

$comment_count = 0;
$comment_reply_count = 0;

// Finding all of the comments
$search_for_article = mysql_query("SELECT * FROM article_comments WHERE article_id = '$article_to_show_id' AND reply_id = '0'");

while($row_comment = mysql_fetch_array($search_for_article))
{
// format the submit updat date right
$comment_date_edit = $row_comment[comment_date];

$comment_date_edit = date('F j, Y \a\t h:ia', $comment_date_edit);

$row_comment[comment_date] = $comment_date_edit;

echo '
<br>
<br>
COMMENT:<br>

<div>
By: ', $row_comment[username] ,' on ', $row_comment[comment_date] ,'
</div>

<div>
', $row_comment[comment] ,'
</div>
';
$comment_count++;

// Finding all of the comment replies if any
$search_for_article = mysql_query("SELECT * FROM article_comments WHERE article_id = '$article_to_show_id' AND reply_id = '$row_comment[id]'");

while($row_two = mysql_fetch_array($search_for_article))
{
// format the submit updat date right
$comment_date_edit = $row_two[comment_date];

$comment_date_edit = date('F j, Y \a\t h:ia', $comment_date_edit);

$row_two[comment_date] = $comment_date_edit;

echo '
<br>
<br>
COMMENT REPLY:<br>

<div>
By: ', $row_two[username] ,' on ', $row_two[comment_date] ,'
</div>

<div>
', $row_two[comment] ,'
</div>
';
$comment_reply_count++;
}
}

Need some help I have 2 tables in a database and I need to search the first table and use the results from that search, to search another table, can this be done? and if it can how would you recommend that I go about it?

Thanks For Your Help Guys! 

Hey guys I need some help for making some things work. This is my index.php and as you can see I have a table of my sql table and a drop list. Now when I press Add to chart on the table on my website, it goes to the cart. But when I select a item from the drop list and click on Add to chart, there only happens an increment of the quantity but the item does not go to the cart.

http://fhcs.be/cart-demo2/index.php  this is my website where you can check the problem out. Thanks Guys!!!!

Code: [Select]
<?php
// Include MySQL class
require_once('inc/mysql.class.php');
// Include database connection
require_once('inc/global.inc.php');
// Include functions
require_once('inc/functions.inc.php');
// Start the session
session_start();
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>PHP Shopping Cart Demo &#0183; Bookshop</title>
<link rel="stylesheet" href="css/styles.css" />
</head>

<body>

<div id="shoppingcart">

<h1>Your Shopping Cart</h1>

<?php
echo writeShoppingCart();
?>

</div>

<div id="booklist">

<h1>Books In Our Store</h1>

<?php
$sql = 'SELECT * FROM products ORDER BY id';
$result = $db->query($sql);
$output[] = '<ul>';
while ($row = $result->fetch()) {
$output[] = '<li>"'.$row['name'].': &euro;'.$row['price'].'<br /><a href="cart.php?action=add&id='.$row['id'].'">Add to cart</a></li>';
}
$output[] = '</ul>';
echo join('',$output);
?>
<?


$Link = mysql_connect("fhcs.be.mysql","fhcs_be","xxxx");
$Query = "SELECT * FROM products";
$DBName = "fhcs_be";
$results = mysql_db_query($DBName, $Query, $Link);



?>
Select a name
<select name = "drop1" size="1">
<Option Value=" ">Select One:</option>
<? //Begins PHP

for($u=0;$u<mysql_num_rows($results); $u++) {
$ID=mysql_result($results,$u,'name');
?>



<option value="<? echo($ID); ?>"><? echo($ID); ?></option><? //Begins PHP
 //Begins PHP
}
?>
</select>

<? //Begins PHP
if (mysql_db_query ($DBName, $Query, $Link)) {

}

else {
print ("FAILURE<BR>\n");
}


mysql_close ($Link);




?>


</div>

</body>
</html>

Hi im trying to make it get the server from the toplist data by the server owner, after that i want it to check if theres more then 1 server if there is i want it to add all the votes together and if there isnt more then 1 i want it to show the votes

<?php
$votes = mysql_query("SELECT * FROM toplist WHERE serverowner='{$_SESSION['user']}'") or die(mysql_error());
    while($vote = mysql_fetch_array($votes)) 
    {
    if(mysql_num_rows($vote) > 1 {
    //gets the server votes and adds them
    } else {
    $servervotes = $vote['votes'];
    }
echo $servervotes; ?>
    }

Hi all, I have a question concerning building the Admin area. Let's say there are 3 pages.
Page 1: "staff.php" (login/register page)
Page 2: "staffproc.php" (process forms)
Page 3: "adminManage.php" (Manage the content, edit, delete, add, etc.)

I need to link the login page to the admin page so no one can access "adminManage.php" without providing the Login details. How??? Will I just include the login page into the admin?

Any response will be appreciated!!

I want to do a mysql query that takes the last two records from two tables and combines them into one row.

So for example if I have a table called "table1" and the latest records are

id - name
35 - chris
36 - chris2
37 - chris3

and a table called "table2" where the latest records are

id - fruit
16 - banana
17 - apple
18 - pear

How can I do a query that will merge the last two rows into one so the result set would be,

id - name    /     id - fruit
37 - chris3   /     18 - pear
36 - chris2   /     17 - apple

Thanks for any help.