September 29, 2013

Log in script load users name

User2827404’s Question:

ive just built a php and mysql log in script which forwards to a members area. I now want the members name that loged in to be displayed, somthing like welcome Stephen for example.

what would be the best way to do this?

ok this is my code once the submit button has been pressed:

`<?php

$host="localhost"; // Host name
$username="stephen2_phptest"; // Mysql username
$password="********"; // Mysql password
$db_name="stephen2_phptest"; // Database name
$tbl_name="registers"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// username and password sent from form
$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];


// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT * FROM registers WHERE email='$myusername' and password='$mypassword'";
$result=mysql_query($sql);

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);

// If result matched $myusername and $mypassword, table row must be 1 row

if($count==1){

// Register $myusername, $mypassword and redirect to file "login_success.php"
session_register("myusername");
session_register("mypassword");
header("location:members.php");
}
else {
echo "Wrong Username or Password";
}
?>`

And this is the code for the members area:

`<?php

$host="localhost"; // Host name
$username="stephen2_phptest"; // Mysql username
$password="********"; // Mysql password
$db_name="stephen2_phptest"; // Database name
$tbl_name="registers"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
session_start();
if(!session_is_registered(myusername)){
header("location:index.php");

$myusername=$_POST['myusername'];
}
?>
<html>
<link rel="stylesheet" type="text/css" href="../php/css/styles.css">
<body>
    <div class="members-screen">
Login Successful</br>
Welcome [persons name to load here]<?php echo $_POST['myusername'] ?> <a href="../php/logout.php"> | Logout</a>
<div class="menu">
    <div class="menu-btn">
        <a href="">Home</a>
        </div>
        <div class="menu-btn">
            <a href="">Search</a>
            </div>
            <div class="menu-btn">
                <a href="">Messages</a>
                </div>
                <div class="menu-btn">
                    <a href="">Matches</a>
                    </div>
                    <div class="menu-btn">
                        <a href="">My Account</a>
                        </div>
    </div>
</div>
</body>
</html>`

Store the name on the session when the user logs in and use it when it is needed to be showed.

session_start();

// Your login process

if($valid == true) {
   $_SESSION['logged_user'] = 'stephen'; // Fetch name from database
}

Then where you need to show:

session_start();
echo $_SESSION['logged_user'];
February 24, 2013

How do I structure a bunch of items that chain together in a MySQL database?

Question by HartleySan

I apologize for the vagueness of my question title, but I don’t even know what to call what I’m trying to accomplish.

The best way to describe what I want is that I want to be able to chain a bunch of items together, and then (possibly) recursively find all the items that are part of any chains that contain the target item. For example, note item3 in the following chains:

item1 => item2 => item3 => item4
item5 => item3 => item6  
item3 => item7 => item8  
item3 => item9 => item10  
item11 => item12 => item13 => item3

If a user were to do a search for item3, then I’d want all five chains above to be displayed. In other words, I want to be able to find all descendants and ancestors of item3, so that I can display the data in an HTML table (or whatever HTML structure works best).
The thing that makes this tricky is that (as shown above) any given item may have many descendants and many ancestors. As such, I’m not sure that regular recursion in MySQL would work.
I did have a look at both of the articles linked to in the top answer for the following SO thread, but I don’t think that the suggested solutions will work for my desired data structure:
Mysql recursion?

Is there any way to structure this kind of data into a MySQL DB so that with fairly easy and lightweight queries (i.e., hopefully one query per item request), I can get the information and structure I’m looking for?
Thank you very much.

Answer by Starx

I have a suggestion.

Store item in the following structure.

+---------+-----------+
|   id    |    item   |
+---------+-----------+
|   1     |   item3   |
+---------+-----------+

And add the link references in the following

+---------+-----------+------------+
|  itemid |  ancestor | descendant |
+---------+-----------+------------+
|  1      |  3        | 2          |
+---------+-----------+------------+
|  1      |  5        | 7          |
+---------+-----------+------------+

Create a index on all three columns. This will enable you to add same time as many times as it appears on a chain.
Also you can query a particular item to find all its related links.

June 18, 2012

Creating an Array with PHP and MySQL

Question by user1114330

I have looked at several posts on stackoveflow for creating an array and haven’t found what I was looking for yet…so many different answers I would like to attempt and get one clear that is closest to what I am trying to accomplish.

Say I want to create an array using this query:

“select * from products”

How is this accomplished most efficiently?

Thank you.

PS – note that I am starting from scratch.

UPDATE

My config file:

`[root@CentOS testphp]# vi config.php
<?php
// Connection's Parameters
$db_host="localhost";
$db_name="tablename";
$username="username";
$password="password";
$db_con=mysql_connect($db_host,$username,$password);
$connection_string=mysql_select_db($db_name);
// Connection
mysql_connect($db_host,$username,$password);
mysql_select_db($db_name);
?>`

The php code I am trying to create the array with…I am getting a Syntax Error:

Parse error: syntax error, unexpected T_VARIABLE in /var/www/html/testphp/test.php on line 2

I get the same error:

`[root@CentOS testphp]# vi test.php
<?php include('config.php')
$query = "select * from upload_products";
$dataArray = array();
$result = mysqli_query($query, $link);
while($row = mysqli_fetch_assoc($query)) {
$dataArray[] = $row;
}
var_dump($dataArray); //Now you have your array.
?>`

Any ideas…It feels like I am missing something here. And, yes I have read the documentation…used their code line by line, reviewed the code and still get the same error as I do with all the code examples I have found on the web.

Answer by Starx

There is not definite way to convert a database result object into an associative array directory. You will have to create this array your result.

$query = "...";
$dataArray = array();
$result = mysqli_query($query, $link);
while($row = mysqli_fetch_assoc($query)) {
    $dataArray[] = $row;
}
var_dump($dataArray); //Now you have your array.
May 7, 2012

What is wrong with this query? Why are results different locally?

Question by StackAttack

I am writing a php site using a mysql database for a class of mine and cannot for the life of me figure out what is wrong with it. I have a query that works locally on my machine (on an identical db to the one on the teacher’s server) but when I upload it, it doesn’t work. The problem is that the query is returning 0 results even though the db has info in it that should be showing.

function bigAssQuery($whereCondition)
{

    $queries[] = 'CREATE TEMPORARY TABLE subAssignments (SELECT ua.assignmentid, ua.assignmentnum, ua.description
                    FROM Course c JOIN UserAssignment ua ON ua.crn = c.CRN AND ua.term = c.term
                    WHERE c.CRN = "'.$_SESSION["crnum"].'" AND c.term = "'.$_SESSION["mysem"].'")';

    $queries[] = 'CREATE TEMPORARY TABLE subStudents (SELECT s.studentid, s.lastname, s.firstname
                    FROM Course c JOIN Student s ON s.crn = c.CRN AND s.term = c.term
                    WHERE c.CRN = "'.$_SESSION["crnum"].'" AND c.term = "'.$_SESSION["mysem"].'")';

    $queries[] = 'CREATE TEMPORARY TABLE subRubric(SELECT assignmentid, re.rubricelementid, re.learning_goal_char
                    FROM RubricElement re JOIN RubricAssignmentRelation rar ON re.rubricelementid = rar.rubricelementid)';

    $queries[] = 'CREATE TEMPORARY TABLE subAssignRub(SELECT subAssignments.assignmentid, rubricelementid, learning_goal_char, assignmentnum, description
                    FROM subRubric JOIN subAssignments ON subAssignments.assignmentid = subRubric.assignmentid)';

    $queries[] = 'CREATE TEMPORARY TABLE subAssignRubStud (SELECT *
                    FROM subAssignRub CROSS JOIN subStudents)';

    $queries[] = 'CREATE TEMPORARY TABLE subAssignInstRubStud (SELECT sars.assignmentid, ai.ainstanceid, rubricelementid, learning_goal_char, assignmentnum, description, sars.studentid, lastname, firstname
                    FROM subAssignRubStud sars LEFT JOIN AssignmentInstance ai ON sars.studentid = ai.studentid AND sars.assignmentid = ai.assignmentid)';

    $queries[] = 'CREATE TEMPORARY TABLE subTotal (SELECT assignmentid, siars.ainstanceid, s.ainstanceid As scoreAID, siars.rubricelementid, learning_goal_char, assignmentnum, description, studentid, lastname, firstname, score
                    FROM subAssignInstRubStud siars LEFT JOIN Score s ON siars.ainstanceid = s.ainstanceid AND siars.rubricelementid = s.rubricelementid
                    ORDER BY lastname, assignmentid)';

    $queries[] = 'SELECT *
        FROM subTotal
        '.$whereCondition.' Order By lastname, assignmentnum, learning_goal_char';  

    return($queries);
}

Then when the db is queried the code looks like this. . .

$queries = bigAssQuery($whereCondition);


$result = 1;
foreach($queries as $query)
{
    $result = $db->query($query);

    if(!$result)
    {
        echo '<script type="text/javascript"> 
                window.onload=function(){ alert("Error: Could not extract course information. Please try again later."); } 
             </script> ';
        exit;
    }
}

$num_rows = $result->num_rows;

I assure you that the local and remote databases are identical. I see no reason why no results are coming back. I did test a few simple temp tables to see if the server wasn’t reading those tables for some reason, but they weren’t an issue in my tests. I would try with nested subqueries, but it gets so convoluted so quickly that I can’t organize it. Maybe there is a better way?
Also, just to clarify the queries aren’t failing, they just aren’t returning anything when I know that they should.
I apologize for the wall of text, but any help is appreciated.

EDIT: I really don’t know which of the queries the problem lies. I do know that I’m probably missing some important information. Part of that lies in my web inexperience. I test locally first because I’ve got the debugger working, but I honestly don’t know how to do remote debugging. I’m using netbeans and xdebug. If someone could suggest a how to get remote debugging set up I would probably be able to come up with some better data. Any suggestions would be helpful

EDIT AGAIN: Found the problem. Embarrassingly enough it was an error in data entry; one of my foreign keys was incorrectly entered. Thanks everybody for pointing me in the right direction.

Answer by Starx

On having a quick look, your code is stoping the execution of the PHP inappropriately. You should at least the let the remainder to continue. Simply exit out of loop using break; instead.

if(!$result)
{
    echo '<script type="text/javascript"> 
            window.onload=function(){ alert("Error: Could not extract course information. Please try again later."); } 
         </script> ';
    break; //exit the loop only NOT THE PHP's Execution
}

Furthermore, check every query individually and run them separately on phpMyAdmin to see, if they are executing correctly. Find the break point and fix the error.

May 3, 2012

COUNT() function each time, or store the value and increment it by one?

Question by john

I have a database with a user ‘votes’ table and a ‘user’ table. I’m thinking the database will get quite big in a small amount of time so i want to use the most efficient method.

I’m thinking i can either COUNT() the amount of votes with a WHERE statement from the ‘votes’ table every time, or i can store the score in the ‘user’ table and just increment it by 1 every time a vote is added.

Which would be best/quickest, and/or are there any other ways of doing this?

Answer by Starx

If you are thinking of the best way to do it. You have to look into optimizing and caching a lot.

I would say,
Create a column on the user tables to store cached score, but maintain the score on the separate table.

Whenever score changes operate of scores table and trigger an update on user’s table with the latest score result.

Doing this, you have extendability in your score data to, kind of like what stackoverflow uses for votes.

April 20, 2012

PHP Session Variables – passing through pages

Question by ez007

Im hoping someone can point me in the right direction of where im going wrong as I feel like im going around in circles!

Im putting together a simple shopping applications – its only very basic at the moment to demonstrate techniques.

The scenario is that there is one database table with items in. They have been split into a blue and red range of items.

On the index page the user has the option of going to either the blue or red items.

Once on the red (or blue) items page, items are displayed and current price and stock level is pulled from the database (MySQL). The user then selects one item and clicks the buy button to add it into their cart.

The page then redirects to the shopping cart page where the user can either update the quantity of the item, proceed to the checkout page or return to the ‘red’ or ‘blue’ ranges.

My issue is this…..

1) How do I set up my session array to capture the items as they are added on the buy ‘click’?

So far I have this on the top of all pages…

<?php session_start();
?>

However only one item seems to be able to be added to the ‘cart’.

This is how im pulling items from my DB:

<?php
$result = mysql_query ('SELECT * FROM items WHERE colour="red"');
// fetch the resulting row as an associative array
while ($row = mysql_fetch_assoc ($result)){
  echo '£', number_format( $row ['price'] / 100, 2, '.', ' ' );
  }
?></p>

2) This is the code for the form action under each item on either the red or blue page.

<form method="post" action="cart.php">
                    <p>
<input type="submit" name="submit" value="Buy" />
<input type="hidden" name="cart" value="add" />
<input type="hidden" name="item" value="redplate" />
                    </p>
                </form>

3) How do I display the ‘ordered’ item in the checkout page after any quantity updates on the shopping cart page?

So far this is what it on the shopping cart page – would I repeat this on the checkout page pulling with it the updated quantity??….

<?php
$submit = $_POST["submit"];

//Call the function and save all data into the array $_SESSION['form'] 
if($submit == "Buy"){setSessionVars();} 

function setSessionVars() {

    $item = array();
    foreach($_POST as $fieldname => $fieldvalue) {
        $item[$fieldname] = $fieldvalue;
    }  
    $_SESSION['cart'] = $item;

          echo "            <table> 
          <tr> 
            <td> 
                <img src="images/{$item['item']}.jpg" />
                    <td/> 
            <td>
                {$item['item']} =
                    </td>
            <td> 
            <input type="text(5)" name="value" value="1" />

            <input type="submit" name="puchasedquan" value="Click to update" /> 

             </td> 
            </tr> 
                            </table>";
}
?>

Any help would be greatly appreciated!! I feel as if i’m traveling around in circles!

Answer by Starx

The big mistake here is, you are putting all you datas on one session variable altogether, i.e. $_SESSION['cart'].

Since you want to insert multiple item on the sessions, you have use $_SESSION['cart'][] to insert the items.

Whenever you are trying to get the values stored, again use a for loop to read as well as .

foreach($_SESSION['cart'] as $cartItem) {
  $cartItem; // will have all the item individually on each pass
}
April 15, 2012

Importing XML in to phpmyadmin database

Question by paulyay

I’m trying to import XML from this site http://data.gov.uk/dataset/car-parks to a phpmyadmin database, so I can use it in a google maps mashup.

I’m new to this and not sure how to go about getting the XML in to the database. Do I create the database columns first and then import the data?

Answer by Starx

There is a LOAD XML method in mysql, through which you can import the data from XML into your database.

An Example:

LOAD XML LOCAL INFILE '/pathtofile/file.xml' 
INTO TABLE `tablename` (fieldl1, field2, ...);
April 14, 2012

When to load database in php

Question by EvilDesire

This is a general question.

I have a website with a page showing many elements of my database.
And in this page I have a script where I’m loading these elements.

The the thing I want to know is it alright to load the database at the display of the page, knowing that everytime user will click and reload the page, my script will reload everything again from my database?

Is it a proper way to do it? Like creating an init.php at the first loading of the website, and storing my elements into a $_SESSION?

I don’t know if I managed to explain my problem, so please ask me more informations if needed.

Answer by Starx

The concept is pretty simple.

If you are sending a new request, it is wise to always start a new connection when needed and close it when its done.

But do not store the connection or any results in SESSION, as this can cause security issues. Creating a common script to start adn close a connection is the way to go.

Having said that look at persistent connections is it probably the thing you want.

April 11, 2012

mysql database insert is changing all IDs to 4294967295

Question by three3

Something really weird is going on with my database. I am using PHP to insert data into my database and I have been doing this for the past 2 years without any problems. When a customer makes a payment on my website, I store all of the data from that transaction in my database. Each transaction has a unique “transaction_id”. When I insert the payment information into the database, all of the information is correctly inserted except for the “transaction_id”. ALL transactions are given the “transaction_id” of “4294967295”. So I did some testing. Here is what I did:

1) I echoed out the “transaction_id” to my screen to see what is would say. The results were that the “transaction_id” that was being echoed out was CORRECT. It was not the repeating “4294967295”. However, when I look in my database, it shows “4294967295”.

2) This time I decided to echo out the query to my web browser. The query was CORRECT. In the query, the CORRECT “transaction_id” was in the query. However, when I look in my database, it shows “4294967295”.

I have 3 different pages where customers can make payments. ALL 3 pages started doing this on April 6th, 2012. None of those pages were modified at all. I have not modified those pages in over 2 years. Any help is greatly appreciated!

$query = "INSERT INTO payments (customer_id, transaction_id, invoice_number, authorization_code, subscription, subscription_id, avs_result, cvv_result, amount, full_tuition, payment_date, ip_address) VALUES ({$_SESSION['customer_id']}, {$_SESSION['transaction_id']}, {$_SESSION['invoice_number']}, '{$_SESSION['authorization_code']}', '{$_SESSION['subscription']}', {$_SESSION['subscription_id']}, '{$_SESSION['avs_result']}', '{$_SESSION['cvv_result']}', {$_SESSION['amount']}, {$_SESSION['full_tuition']}, '{$_SESSION['payment_date']}', '{$_SESSION['ip_address']}')" ;
$result = mysqli_query($dbc, $query) OR die ('<p>There was an error with the INSERT payments query.: ' . mysqli_error($dbc) . '<br />Query:' . $query . '</p>') ;

echo '<p>Transaction ' .  $_SESSION['transaction_id'] . ' has been <font color="green">APPROVED</font> by the system.</p>' ;

echo '<br /><br />' ;

echo '<p>Below is a summary:</p>' ;
echo '<p>Transaction ID: ' .  $_SESSION['transaction_id'] . '<br />
Payment Method: XXXX<br />
Amount: $' . $amount . '<br />
Customer Name: ' . $_SESSION['first_name'] . ' ' . $_SESSION['last_name'] . '<br />
</p>' ;

echo "<p>Note: Please do NOT click the browser's Back button to enter a new transaction.</p>" ;


echo $query ;

Answer by user282172

Your number is larger than the field in the DB can handle…

4294967295 is the largers number 32 bits can hold, your transaction ID is now larger than the numerical field your DB can hold.

Answer by Starx

Change the data type of the transaction_id to BIGINT

April 10, 2012

From Database language to PHP

Question by FGatlin

I need some help please. I have this from my database:

SELECT DISTINCT 
    iwi.item Item, ppd.item_des Des, iwi.qty_on_hand QOH, iwi.qty_allocated QA, 
    iwi.qty_on_order QOO, iwi.min_qty Minimum, MAX(ppm.expected_date)Exp_Date
FROM inv_warehouse_items iwi, pur_po_master ppm, pur_po_detail ppd)
WHERE IWI.CO = '100'
AND iwi.item_key = ppd.item_key
AND ppm.po_key = ppd.po_key
AND iwi.warehouse = '01'
AND iwi.item IN ('BXHP335',
'BXHP435',
'BXHP535',
'BXHP644',
'BXHP743',
'BXHP965',
'BXHP10741',
'BXHP10748',
'BXHP1253',
'BXHP1257',
'BXHP121210',
'BXHP151410',
'BXHP16114',
'BXHP181411',
'BXHP241612',
'BX1195',
'BX6636',
'BXHP201512',
'BXHP1949',
'BXHP2015',
'BXHP201515',
'BXHP351011',
'BXHP241814',
'BXHP2257',
'BXHP1824')

GROUP BY iwi.item, iwi.qty_on_hand, iwi.qty_allocated, iwi.qty_on_order, 
     iwi.min_qty, ppd.item_des
ORDER BY iwi.item"

And I need it to print out with php.

Answer by Starx

May be like this

$query = "...";
$result = $mysqli_query($db, $query);
while($row = mysqli_fetch_assoc($result)) {
    print_r($row);
}
...

Please fill the form - I will response as fast as I can!