PHP - Mysql Convert Currencies And Sum Group By Branch

Hey all I've found some combination functions out there but they're not working really like I want.

I have an array with some currencies:

array('SEK', 'EUR', 'USD', 'GBP', 'DKK', 'NOK', 'ISK');

Now I want the function to generate an array with all possible pairs:

array(array('SEK', 'EUR'), array('SEK', 'USD') ...)

It must allow for the inverse form: EUR, SEK is different from SEK, EUR.

But trash all pairs that are equal: SEK, SEK or EUR, EUR.

Help me out?

Hi,
i have 2 currencies in my mysql and when user add new article, he choose currency and price.
Now i need search engine for searching articles, and i have problem in part when user choose to show only articles between 100 and 200 euros, so i need help to convert all currencies to euro(all in mysql query) and show from 100 to 200 dollars, so if article price is 100 euros, then to show it too.
Can someone help me ?
1 euro = 0.70 $ (example for values)

Hi Everyone,

I'm trying to convert my MySql data into variables so I can use them on my website. For example, if I want to convert a city or even first or last name into a variable. How would I go about doing that? Thanks everyone!

Hi, I am trying to convert the register & login script from mysql to mysqli. I have converted the easy parts and have the connection to the database, but the following functions all need changing and I can't work out the correct solution mainly due to the deprecation of mysql_result()

The code that needs updating is
 

<?php
function user_count() {
	return mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `active` = 1"), 0);
}

function users_online() {
	return mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `logged_in` = 1"), 0);
}

function change_profile_image($user_id, $file_temp, $file_extn) {
	$file_path = 'images/profile/' . substr(md5(time()), 0, 10) . '.' . $file_extn;
	move_uploaded_file($file_temp, $file_path);
	mysql_query("UPDATE `users` SET `profile` = '" . mysql_real_escape_string($file_path) . "' WHERE `user_id` = " . (int)$user_id);
}

function has_access($user_id, $type) {
	$user_id 	= (int)$user_id;
	$type 		= (int)$type;
	
	return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `user_id` = $user_id AND `type` = $type"), 0) == 1) ? true : false;
}

function activate($email, $email_code) {
	$email 		= mysql_real_escape_string($email);
	$email_code = mysql_real_escape_string($email_code);
	
	if (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email' AND `email_code` = '$email_code' AND `active` = 0"), 0) == 1) {
		mysql_query("UPDATE `users` SET `active` = 1 WHERE `email` = '$email'");
		return true;
	} else {
		return false;
	}
}

function user_exists($username) {
	$username = sanitize($username);
	$query = mysql_query("SELECT COUNT('user_id') FROM `users` WHERE `username` = '$username'");
	return (mysql_result($query, 0) == 1) ? true : false;
}

function email_exists($email) {
	$email = sanitize($email);
	return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email'"), 0) == 1) ? true : false;
}

function user_id_from_username($username) {
	$username = sanitize($username);
	return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `username` = '$username'"), 0, 'user_id');
}

function user_id_from_email($email) {
	$email = sanitize($email);
	return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `email` = '$email'"), 0, 'user_id');
}

function login($username, $password) {
	$user_id = user_id_from_username($username);
	
	mysql_query("UPDATE `users` SET `logged_in` = 1 WHERE `user_id` = $user_id");

	$username = sanitize($username);
	$password = md5($password);
	
	return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `username` = '$username' AND `password` = '$password'"), 0) == 1) ? $user_id : false;
}

?>

And here is what the converter gave me:

function user_count() {
return mysql_result(mysqli_query($GLOBALS["___mysqli_ston"], "SELECT COUNT(`user_id`) FROM `users` WHERE `active` = 1"), 0);
}

function users_online() {
return mysql_result(mysqli_query($GLOBALS["___mysqli_ston"], "SELECT COUNT(`user_id`) FROM `users` WHERE `logged_in` = 1"), 0);
}

function change_profile_image($user_id, $file_temp, $file_extn) {
    $file_path = 'images/profile/' . substr(md5(time()), 0, 10) . '.' . $file_extn;
    move_uploaded_file($file_temp, $file_path);
    mysql_query("UPDATE `users` SET `profile` = '" . mysql_real_escape_string($file_path) . "' WHERE `user_id` = " . (int)$user_id);
}

function has_access($user_id, $type) {
    $user_id     = (int)$user_id;
    $type         = (int)$type;
    
    return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `user_id` = $user_id AND `type` = $type"), 0) == 1) ? true : false;
}

function activate($email, $email_code) {
    $email         = mysql_real_escape_string($email);
    $email_code = mysql_real_escape_string($email_code);
    
    if (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email' AND `email_code` = '$email_code' AND `active` = 0"), 0) == 1) {
        mysql_query("UPDATE `users` SET `active` = 1 WHERE `email` = '$email'");
        return true;
    } else {
        return false;
    }
}

function user_exists($username) {
    $username = sanitize($username);
    $query = mysql_query("SELECT COUNT('user_id') FROM `users` WHERE `username` = '$username'");
    return (mysql_result($query, 0) == 1) ? true : false;
}

function email_exists($email) {
    $email = sanitize($email);
    return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email'"), 0) == 1) ? true : false;
}

function user_id_from_username($username) {
    $username = sanitize($username);
    return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `username` = '$username'"), 0, 'user_id');
}

function user_id_from_email($email) {
    $email = sanitize($email);
    return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `email` = '$email'"), 0, 'user_id');
}

function login($username, $password) {
    $user_id = user_id_from_username($username);
    
    mysql_query("UPDATE `users` SET `logged_in` = 1 WHERE `user_id` = $user_id");

    $username = sanitize($username);
    $password = md5($password);
    
    return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `username` = '$username' AND `password` = '$password'"), 0) == 1) ? $user_id : false;
}

?>

Please could someone point me in the right direction here?     Also my site works perfectly well with MySQL, do I have to convert it to MySQLi?   Many Thanks   Paul If anyone knows how to solve this, it would be much appreciated. I already have a website template and would prefer to continue with mysqli instead of PDO.

Many Thanks

Paul

Hello guys,
I have purchased a new book for PHP called Professional PHP 6. But the bad news is that the whole book was written for PostgreSQL NOT for MYSQL which I'm familiar with!

I have this code, I have tried to do so many things to get it working! but nothing seems to have it working!
Long story short, I have failed to convert the code to work with MySQL!

Here is my code, in case some one will offer a help, or a reference to go to if some similar case comes up on my way.

<?php
class Widget {

private $id; 
private $name; 
private $description; private $hDB; 
private $needsUpdating = false;

public function __construct($widgetID) { 
//The widgetID parameter is the primary key of a 
//record in the database containing the information 
//for this object
//Create a connection handle and store it in a private member variable 
//This code assumes the DB is called "parts" 
$this->hDB = pg_connect('dbname=parts user=postgres'); 
if(! is_resource($this->hDB)) {
throw new Exception("Unable to connect to the database.");
}
$sql = "SELECT name, description FROM widget WHERE widgetid = $widgetID";
$rs = pg_query($this->hDB, $sql); 
if(! is_resource($rs)) {
throw new Exception("An error occurred selecting from the database.");
}
if(! pg_num_rows($rs)) {
throw new Exception("The specified widget does not exist!");
}
$data = pg_fetch_array($rs); 
$this->id = $widgetID;
}

public function getName() { 
return $this->name;
}

public function getDescription() { 
return $this->description;
}

public function setName($name) { 
$this->name = $name; 
$this->needsUpdating = true;
}

public function setDescription($description) { 
$this->description = $description; 
$this->needsUpdating = true;
}
public function __destruct() { 
if($this->needsUpdating) {
$sql = "UPDATE widget SET "; 
$sql .= "name = " .  pg_escape_string($this->name) . ", "; 
$sql .= "description = " . pg_escape_string($this->description) . ""; 
$sql .= "WHERE widgetID = " . $this->id;
$rs = pg_query($this->hDB, $sql);
}
pg_close($this->hDB);
}
}
?>



FYI: I have tried mysql_pconnect ! results => failure!
I have tried replacing the prefix "pg" to "mysql" or "mysqli"! results => failure!
I have tried switching parameters, say in pg_query($resource, $query) TO mysql_query($query, $resource)  results => failure too!




Thank you in advance!

I've been playing around with this code from http://v3.thewatchmakerproject.com/journal/276/building-a-simple-php-shopping-cart

Objective: I'm trying to make the scripts work but without pulling the data from MySQL Database.
Instead, I created an array and inserted some info there and trying to replicate the same results but getting confused.

For example, on the Index.php file, I made the following changes:


<?php

// Start the session
session_start();

// Include functions
require_once('inc/functions.inc.php');

// Products multidimensional Array
$products = array("book" => array(  'id' => '1',
                                    'title' => 'Learn Your ABC\'s',
                                    'author' => 'John Doe',
                                    'price' => '14.95'),
                    "video" => array('id' => '2',
                                    'title' => 'Visual Guide to learn',
                                    'author' => 'Adam Smith',
                                    'price' => '21.38'),
                    "puzzle" => array('id' => '3',
                                    'title' => 'Can You Solve?',
                                    'author' => 'Sara Brown',
                                    'price' => '9.41'),
                    "exam" => array('id' => '4',
                                    'title' => 'Test Your Knowledge',
                                    'author' => 'Kim Carver',
                                    'price' => '11.15'));


?><!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 &#38;#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 books ORDER BY id';
 $result = $db->query($sql);
$output[] = '<ul>';
while ($row = $result->fetch()) {
$output[] = '<li>"'.$row['title'].'" by '.$row['author'].': $'.$row['price'].'<br /><a href="cart.php?action=add&id='.$row['id'].'">Add to cart</a></li>';
}
$output[] = '</ul>';
echo join('',$output);
*/
?>

<?php
$output[] = '<ul>';
foreach ($products as $product => $product_item) {
// $flavor is the key and $piece is the value
foreach ($product_item as $item => $piece) {

    if ($item == "id") {                            # Conditional logic defined to map specific keys to specified name value
        $row['id'] = $piece;
//        echo "The id is ". $id."<br />";                            // set just to confirm value correct for variable
    }  elseif ($item == "title")  {                        # Conditional logic defined to map 1st key to price value
        $row['title'] = $piece;
//        echo "The title is ". $title."<br />";                            // set just to confirm value correct for variable
    }  elseif ($item == "author")  {                        # Conditional logic defined to map 1st key to price value
        $row['author'] = $piece;
//        echo "The author is ". $author."<br />";                            // set just to confirm value correct for variable
    }  elseif  ($item == "price")   {                    # Conditional logic defined to map 1st key to shipping value
        $row['price'] = $piece;
//        echo "Price: ". $price."<br /><br />";                        // set just to confirm value correct for variable
    }

$output[] = '<li>"'.$row['title'].'" by '.$row['author'].': $'.$row['price'].'<br /><a href="cart.php?action=add&id='.$row['id'].'">Add to cart</a></li>';
$output[] = '</ul>';
echo join('',$output);

}
}

?>
</div>

</body>
</html>


And here's the original index.php before my edits which show the sql query to the db:


<?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 &#38;#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 books ORDER BY id';
$result = $db->query($sql);
$output[] = '<ul>';
while ($row = $result->fetch()) {
$output[] = '<li>"'.$row['title'].'" by '.$row['author'].': &pound;'.$row['price'].'<br /><a href="cart.php?action=add&id='.$row['id'].'">Add to cart</a></li>';
}
$output[] = '</ul>';
echo join('',$output);
?>

</div>

</body>
</html>


Also, I included the zipped file of the entire script setup.

Again, I'm trying to figure out how to make the cart work with using an array for the product instead of MySQL database.
My goal is to remove ALL SQL statements from the scripts but stuck with the looping portion to display the title, author, and price from the array as shown above.

I have tried a large number of "solutions" to  this but everytime I use them I see 0000-00-00 in my date field instead of  the date even though I echoed and can see that the date looks correct.

Here's where I'm at:
I have a drop down for the month (1-12) and date fields (1-31) as well as a text input field for the year.  Using the POST array, I  have combined them into the xxxx-xx-xx format that I am using in my field as a date field in mysql. 

<code>
$date_value =$_POST['year'].'-'.$_POST['month'].'-'.$_POST['day'];
echo $date_value;
</code>

This outputs 2012-5-7 in my test echo but 0000-00-00 in the database.

I have tried unsuccessfully to use in a numberof suggested versions of:
strtotime()
mktime

Any help would be extremely appreciated.
I am aware that I need to validate this data and insure  that it is a valid date. That I'm okay with. I would like some help on getting it into the database.

Hi

I need to change a uk date dd/mm/yyyy to mysql format yyyy/mm/dd

I have a user form where they enter the date in dd/mm/yyyy string format, i want to take that string, and convert it to mysql format yyyy/mm/dd

Possible?

Ive searched for ages and havent found a solution that works.

Is there an easy solution to this? surely there must be

Cheers

Hi,

I have database, screenshot attached. I have to print all records of attendance sort by ID and have sum group by ID. How to know or figure out when the ID changes so that i can show the total for that ID. Bare with me please and try to show me where is my mistake!

Code:

<?Php

	include('session.php');
	include('dbcon.php');
	
?>

<html>
<head>
<link rel="stylesheet" type="text/css" href="bootstrap.css">
</head>

<body>
	
	<div class="container">
		<div class="row">
			<div class="col m-auto">
				<div class="card mt-5">
					<table class="table table-bordered">
					
						<tr>

							<input type="button" onClick="window.print()" value="Print The Report"/>
							<td>OracleID</td>
							<td>Name</td>
							<td>Designation</td>
							<td>Clocking In Time</td>
							<td>Clocking Out Time</td>
							<td>Duration</td>
						
						</tr>
						
						<?php
						
							$isdone = -1;
							
							$query = "select * from attendance_records where isdone= '".$isdone."' order by OracleID";
							$result = mysqli_query($con, $query);
																			
							$query2 = "SELECT sec_to_time(SUM(timestampdiff(SECOND, ClockingInDate, ClockingOutDate))) as total from attendance_records group by OracleID";
							$result2 = mysqli_query($con, $query2);
							
							while ($row = mysqli_fetch_assoc($result))
							{
								
								$userid = $row['OracleID'];
								$name = $row['Name'];
								$des = $row['Des'];
								$clockingindate = $row['ClockingInDate'];
								$clockingoutdate = $row['ClockingOutDate'];
								$duration = $row['Duration'];
								$t=time();
								$curdate = date("d-m-Y",$t);
								$curTime = date("g:i:s A",$t);
																		
						?>
						
						<tr>
						
							<td><?php echo $userid ?></td>
							<td><?php echo $name ?></td>
							<td><?php echo $des ?></td>
							<td><?php echo $clockingindate ?></td>
							<td><?php echo $clockingoutdate ?></td>
							<td><?php echo $duration ?></td>
		
						</tr>
						
						<?php									
							
							}	
								while($row2 = mysqli_fetch_assoc($result2))
									{
										$totals= $row2['total'];
									
										echo '<td><h5>Total Attendance Time:</h5></td>';
										echo '<td>';
										echo $totals; 
										echo '</td>';
									}						
										
							
								echo '<tr>';
								echo 'Report created by ', $_SESSION['user_id2'], ' on ', $curdate, ' at ', $curTime;
								
								echo '</tr>';
							
						?>
						
					</table>
					
					<a href="home.php" width="100%">Click here to go back to Main Menu</a>
			
				</div>
			</div>
		</div>
	</div>

</body>

</html>

and screenshot of result is attached. I have the totals but not at the end of each OracleID, it is at the end of the report.

Thanks.

I am trying to get a group by to work but for some reason it will only display one output.  Not sure what the problem is.

Code: [Select]
mpid  mpyear mpmonth mpday  mptitle
  4       2012      3             2        Text stuff here
  3       2012      3             1        Day 1 stuff here
  2       2012      2             28      Feb stuff here
  1       2011      12           27      First post test here


So what I am trying to do is sort out per year month

ie:

2012
3
Text stuff here
Day 1 stuff here
2
Feb stuff here
2011
12
 First post test here
That is what I eventually want it to look like, but first thing I need to do is get atleast the mptitle to group first.
Code: [Select]
$query = "SELECT * FROM monsterpost group BY 'mpmonth'";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo $row['mptitle'];
echo "<br />";
}
?>

Hi all, does anyone know how to perform this, or if it's even possible? I need every 6th row to be a GROUP BY of the following 5 rows, they all share a common group_id so that's what the GROUP BY is performed on.

1. GROUP BY group_id
2. item #1
3. item #2
4. item #3
5. item #4
6. item #5
7. GROUP BY group_id
8. item #6
... and so on

G'day to all,

I need some help building a site that has the ability to accept members and deposit those members in a database.

When the members join they will have the ability to add features to their profile; like, skills and certificates. Upload pictures of certificates and a profile pic.

The admin area needs to have the ability to add membership types and give those members specific abilities.

The admin also needs to have the ability to create profile options, like additional skills.

Like a type 1 member has the ability search and see all type 2 members who have a particular skill set.

type 2 members just get to see their own profile and the tasks the type 1 members list.

 

This all might sound like a bunch of brambles but please let me know if you are interested and we can get more involved.

Thank you so very much for your help in advanced...

Juan

Hi

Just a quick one.

I want my output result to look like this. In its simplistic form I have 2 columns, NAME and NUMBER.

Adam
1
2
3

Becky
1
2
3

Charlie
1
2
3


So the output results are grouped by the "name" field, then ordered by the "number" field.

I thought it was GROUP BY, but that function is something else.

Iv tried searching around but im unsure what you call this form of grouping results.

Many Thanks!

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=306114.0