MySQL Forums - PHP

sum alias column (1 reply)

agus afryadi — Wed, 22 May 2013 04:35:32 +0000

i have query:
SELECT SUM( nilai.pilih1 ) as kpilih1,
SUM( nilai.pilih2 ),
soal.jwba,
soal.jwbb,
soal.jwbc,
soal.jwbd,
nilai.nilai as nilai,
nilai.catatan as catatan

CASE WHEN nilai.nilai = 4 THEN '1'
ELSE '0'
END AS 'tali1',
CASE WHEN nilai.nilai = 3 THEN '1'
ELSE '0'
END AS 'tali2',
CASE WHEN nilai.nilai = 2 THEN '1'
ELSE '0'
END AS 'tali3',
CASE WHEN nilai.nilai = 1 THEN '1'
ELSE '0'
END AS 'tali4'

"MY PROBLEM....
get total of tali1, get total of tali2, get total of tali3, get total of tali4,
"

FROM nilai, soal where soal.idkat = '$program' and soal.standar = '$standar' and soal.id_soal = nilai.id_soal group BY soal.id_soal";

my problem is, ho to sum() alias column from "case" ( sum( tali1 ) get total of tali1, sum( tali2 ) get total of tali2, sum( tali3 ) get total of tali3, sum( tali4 )get total of tali4 ), because mysql can't sum of alias column.
thank's

Create Definer wildcard and login issue (1 reply)

Renato Di Martino — Mon, 20 May 2013 03:31:26 +0000

Hi,
this is my first question here and I honestly made it after long investigation without answers.

I have created an intranet web application that run on a CentOS web server, the application use another local server (always CentOS) for the mysql database.

Inside the database I have created routines wich every start like this:

CREATE DEFINER='renato'@'192.168.0.%' PROCEDURE 'CreateUser'
or
CREATE DEFINER='renato'@'192.168.0.%' PROCEDURE 'LoginUser'

Until here all is ok the application is working on 192.168.0.5 which is the IP of the server where application is installed.

Now some days ago as the server that run the application is an old one, to add another server (I mean commodity server), what I was thinking is to do a Round Robin balance.

So I have added to the network another server (192.168.0.6) and set a round robin balance with a TTL of 1 minute.

And here is the issue, if I login to the system there is no problems since round robin let me login on first or second server, but when I'm inside the application and for example open another link like subdomain the round robin send me on the second server and force me to relogin.

So it's like I can login correctly on one of the two server, but I have to stay on that server I have logged in, otherwise it will force me to login again.

I really don't understand where is the issue, since on the database I have set the wildcard.

Thanks in advance for help
Renato

ubuntu 12.04 php 5.3 and mysql 5.5.31 did not work call store procedure (1 reply)

Игорь Владимирович — Thu, 16 May 2013 18:00:39 +0000

Good day. Please if you know tell me what i am doing wrong. Sorry for my bad English. LAMP server Ubuntu 12.04, MySQL Server 5.5.31, PHP 5.3.10, Apache 2.2 with virtual host, doing simple query in store procedure: select * from table1, but in php script when i doing $res = mysqli_query("Call proc1();"); while ($row =mysqli_fetch_assoc($res)) { var_dump($row)}; empty result, even i doing mysqli_query("Select * from table1;") empty result(((. What happen. May be someone know?

problem in selest asterisk mysql table's fields (1 reply)

Mohammad Nemati Habili — Sun, 12 May 2013 21:55:55 +0000

hi to all
dear friends
i wana have a show from some fields of asterisk mysql database (asteriskcdrdb(cdr table)) in a certain date for example from 2013-4-1 till 2013-5-1
as you know asterisk records all calls information in this table like : calldate,src,dst,duration and ...
i wana show all calls of one extension like 3004 in a certain date
i want all records that 3004 is in src field and 3004 is in dst field(all dialed and recieved calls)
i use below query:

select calldate, src, dst from cdr where calldate between 2013-4-1 and 2013-5-1 and src = '3004' or dst='3004';

the problem is in the result
results do not belong to that date, acctually it shows all calls that 3004 dialed and recieved from the first day that extension creats till today,
its imporatant for me to show results in a specific date
please help me
and if my query has any problem please correct it
plzzzzzzzzzzz

Script that recognises the name of a column in a CSV file and matches it to the correspondent column, with the same name, in a MySql database (1 reply)

Sarah Raniero — Thu, 09 May 2013 02:10:01 +0000

we are using PHP and MySql for our application.

Problem:

we get some data, we put them in a CSV file, we upload them in our database through a template, then we upload the data online.

we have many CSV files with different fields' order and some fields are empty, for example:

CSV1 has the following fields order: name, description, date, address, phone
CSV2 has the following fields order: address, description, name, date
CSV3 has the following fields order: name, address
To upload these data in our database (and then online), we now need to have all the CSVs' fields always in the same order, so for example: name, descrition, phone, address, date. To do this we need to manually move the columns in the CSVs.

My question is:

if the CSV files have the columns in a different order (but with the same field names), is it possible to match a CSV column to its right position in the database, without having to manually edit the position of the columns in the CSV?

So basically a script that recognise the name of a column in a CSV file (for ex. address) and match it (and its content) to the correspondent column in our MySql database.

Many thanks, Sarah

Getting many error in error_log file. (1 reply)

Dilshad Hussain — Wed, 08 May 2013 13:41:36 +0000

Hi Guys....

Does anyone have any idea, why I am getting this in my apache log file......

PHP Warning: PDO::__construct(): [2002] Cannot assign requested address (trying to connect via tcp://ec2-184-72-144-105.compute-1.amazonaws.com:3306) in...................

Referencing Images (8 replies)

Jay Ogre — Wed, 08 May 2013 21:03:37 +0000

First off, thanks to anyone will to spend a moment to reply to my question...

I'm self-taught in HTML and CSS and have coded half-a-dozen websites without any problems. My latest site, however, will have hundreds of pages, each with identical layout (HTML code) except that a single image displayed will be unique to each page. It's a comic strip, so everything around the actual image is the same, but I do need unique addressed that can be referenced for each unique page, so an image viewer is out.

Reading up, I decided that a CMS / database-driven site was what I needed. I tried Wordpress and hate it with a passion, and really just want only the code that I need for this simple task. How hard could it be, right?

So... I bought the book "Murach's PHP and MySQL" http://www.amazon.com/Murachs-PHP-MySQL-Murach-Reference/dp/1890774561/ref=sr_1_1?ie=UTF8&qid=1367961628&sr=8-1&keywords=murach%27s+PHP

... and I'm halfway through it when I realized, although the book is ok, and I'm learning a lot of stuff that's way more complex than what I'll be doing, I'm not actually learning the only thing I need. I flipped around and I realized I'm in over my head at this point.

Can anyone point me to some sample PHP code or tutorials for what I'm looking for?

I just want to be able to use a single HTML page that only swaps the image when a user clicks a link to another page. The reason I want this is because there will be 100s of pages over time, and I'll want to be able to update the HTML as needed without opening and altering 100 files. But I don't need all the other crazy stuff that comes with these all-in-one CMS systems.

Or if anyone has a better idea, I'd really love to hear it.

Again, thanks for anyone's guidance!

application with php and myadmin (1 reply)

Bodelère ASSOKPE — Sat, 04 May 2013 19:40:45 +0000

Hi everyone iam developping an application with php and mysql but i want to use oracle 11g for the datawarehouse can someone help me about how to proceed???? plz help

inserting csv data into mysql table using PHP (2 replies)

John Spencer — Wed, 01 May 2013 16:14:10 +0000

Just looking for some advice on the best way to go about inserting some data(and in the future updating) from a csv file into a mysql db table.

My server doesnt allow the command load data infile so using php just not sure the best approach as my csv file has less columns than the table and different headers than the table.

eg

CSV FILE

'PRODUCT_ID', 'PRICE', 'STOCK', 'DESCRIPTION'

table

products_id, products_price, products_stock, products_status, ect , ect,

Im guessing creating an array from the csv and assigning each column as a $variable then mapping this to the relevant columns in the table then inserting.

there are so many ways to go about the same thing just wanted a bit of advice on which route to take.

thanks for your time.

John.

Seeks Improved Efficency: Redundant Query Data (8 replies)

stanley tweedle — Tue, 14 May 2013 02:30:30 +0000

Greetings. I seek your advisory for the following 2-table simple SQL problem. (i believe, simple, that is). Super-details (eg. Table3), i might add later (Eg. producer, writer, credits), but i need to get the thing moving, first. I've tried to use Std MySQL doc terminology, where applicable (Eg. table1.col1 , table2.col2). Please, don't hesitate to tell me what YOU need / wish to know about my task. Thank you!

Table1, Master:

CREATE TABLE `cp_master` (
  `master_pk` int(11) NOT NULL AUTO_INCREMENT COMMENT 'master table primary key',
  `master_date` date NOT NULL,
  `master_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`master_pk`),
  UNIQUE KEY `master_date` (`master_date`,`master_timestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

Table2, Details:

CREATE TABLE `cp_details` (
  `details_pk` int(11) NOT NULL AUTO_INCREMENT,
  `details_fk` int(11) NOT NULL COMMENT 'insert foreign key',
  `details_date` date NOT NULL,
  `details_toonname` tinytext NOT NULL,
  `details_toonepi` tinytext NOT NULL,
  `details_toontime` time NOT NULL,
  PRIMARY KEY (`details_pk`),
  KEY `details_fk` (`details_fk`,`details_date`)
) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=utf8 COMMENT='Cartoon Planet: Details'

This is a private/ personal DB created for Mgt of DVR media (indeed, @cartoonNetwork’s Cartoon Planet. yikes!).

Using phpMyAdmin to create / admin the thing, i find these queries are quite redundant. Having been much more active in the DB Admin game, years ago in my studies, i'm a bit rusty in terms of
“How might I minimize my query, while maintaining equal results?”

E.G.

INSERT INTO `cartoonplanet`.`cp_details` 
(`details_pk`, `details_fk`, `details_date`, `details_toonname`, `details_toonepi`, `details_toontime`)
 VALUES 
(NULL, '11', '2013-04-05', 
'Courage the Cowardly Dog', 'Klub Katz', '00:27:30');

My thoughts:
First, the timestamp on the master. It's superfluous, from any perspective, truly. I don't /really/ need to know at what time X was entered to Y, etc., but knowing i've usually used TimeStamps for indexing, etc., i stuck it there. Whatever: likely NOT at issue (and would have been of more use, had I placed it instead, in the Table2.)
Second
I'm looking at the query, thinking: i've got a PK (Eg. 11, in the code above), and an index (Eg. 2013-04-05). This is what bothers me. I don't really need both, in my Table2 INSERT Query.
At the crux
In addition to your valued advisory, RE optimization (Eg. you should have placed A at B, and used C instead of D), i wonder: supposing I code a proper interface for this DB, outside of the PMA-zone as ultimately desirable, what is most important, in terms of the data entry, are

table2.`details_date`

and, particular to that bit of data, the

table2.`details_toontime`

, which reflects the counter on the DVR (Eg. 00:30:00 , where 30 -eq 30 mins).

Summary
How might I better submit such a query; prep the table for more efficient PHP; realizing there are multiple perspectives from which to attack this problem, please, how ever you wish to view it, i suspect you understand my general plight.

I've coded PHP / MySQL queries using LEFT JOIN, and INSERT's using SELECT statements. So, i'm not quite at beginner mode here. I'm seeking some feedback, having no one to bounce the topic-off-of, so to speak, other than ye in the cyberverse.

When I look at the query, I think: how am I going to manipulate/ USE this data, from a PHP / interface point-of-view. Should I be placing the table2.ForeignKey-Ref (table2.`details_fk`) AND the date-as-index (table2.`details_date`) in EVERY submit?

I sense i'm encountering a “Which Came First: The Chicken-or-the-Egg scenario”, where my application is going to likely want to See/ rather, Display the dates, not the Keys.
E.G.

 SQL = "INSERT INTO table2 VALUES '','SELECT...','SELECT...','ToonTitle','ToonEpisode','ToonTime'";

But, i'm having difficulty wrapping my head around-it, so to speak (ie. it's been so long since i've done such coding, from a completely home-baked recipe).

Your feedback will be appreciated.

EDIT: SQL Gathered while working so far (gathering into a new Netbeans project, for some sanity)...

Insert a new table1.master row:

$sql = "INSERT INTO `cartoonplanet`.`cp_master` " . 
"(`master_pk`, `master_date`, `master_timestamp`) " .
"VALUES (NULL, \'20XX-XX-XX\', CURRENT_TIMESTAMP);";

Insert a new table2.details row:

$sql = "INSERT INTO `cartoonplanet`.`cp_details` " .
"(`details_pk`, `details_fk`, `details_date`, `details_toonname`, `details_toonepi`, `details_toontime`) ".
"VALUES (NULL, \'11\', \'2013-04-05\', \'Courage the Cowardly Dog\', \'Klub Katz\', \'00:27:30\');";

Find a Toon by Name:

$sql = "SELECT * FROM `cartoonplanet`.`cp_details`\n" . 
"WHERE `details_toonname` LIKE \'TOON_NAME\' ORDER BY `cp_details`.`details_pk` " .
"ASC LIMIT 0, 30 ";

Show what ToonEpi(sodes) are particular to the date(Index):

$sql = 'SELECT *'
        . ' FROM `cp_details`'
        . ' WHERE `details_date` = \'XXXX-XX-XX\';';

best regards!

cannot connect to mysql server (13) (1 reply)

hoff mann — Mon, 29 Apr 2013 14:34:04 +0000

basically, i have a web server (apache in centos installed in host B, and want to connect to a MySQL server in host A) both hosts are in the same subnet.

i can access remote mysql using CLI mysql -u root -h -p

but cannot connect to mysql using PHP function mysql_connect()

i have already disabled SELINUX and system-config-firewall-tui and included below syntax in /etc/my.cnf

[client]
port =3306
socket = /var/lib/mysql/mysql.sock

a lot of solutions found in the web tells me to type in the setbool associated with selinux, but the thing is, SELINUX is already disabled.

the odd thing is, i can connect thru CLI, but not in php function

also, i have php-mysql in both hosts

I'm having extreme difficulty getting my ADD and DELETE buttons to work with PHP and MySQL tables. (1 reply)

Malinda Smith — Fri, 26 Apr 2013 00:55:09 +0000

I need to create a PHP page where I can input form data and see what was entered on the same page and another page that is the data output that will show the table with the data entered. At the bottom of the output page I need a an Update and Delete button for changing and clearing any data in the table. The sample that I experimented with has the input form at the top and the records underneath and the delete button looping at the end of each record row. After experimenting, I separated the coding for the input and output on two separate pages. I got the button out of the loop to just show at the bottom for the delete, but then the data in the table will not show and the delete button doesn't work. The same with the add button on the input form. It now will not add and I'm not sure what I did wrong. I could use help ASAP for I am on a deadline.

THIS IS WHAT I HAVE FOR THE INPUT FORM:

require_once 'login_publications.php';

if(isset($_POST['author']) &&
isset($_POST['title']) &&
isset($_POST['category']) &&
isset($_POST['year']) &&
isset($_POST['isbn']))

{
$author=get_post('author');
$title=get_post('title');
$category=get_post('category');
$year=get_post('year');
$isbn=get_post('isbn');

$query= "INSERT INTO classics VALUES" .
"('$author','$title','$category','$year','$isbn')";

if(!mysql_query($query, $db_server))
echo "INSERT failed: $query
" .
mysql_error() . "

";
}

echo <<<_END



  Author 


   Title 


Category 


    Year 


    ISBN

_END;

function get_post($var)
{
return mysql_real_escape_string($_POST[$var]);
}
?>

THIS IS WHAT I HAVE FOR THE OUTPUT FORM:

require_once 'login_publications.php';

if (isset($_POST['author']) &&
(isset($_POST['title']) &&
(isset($_POST['category'])) &&
(isset($_POST['year']) &&
(isset($_POST['isbn']))

//These two query lines is where I am stuck and after adding the above ISSET and this query statement, I lost the data.
$query = "SELECT * FROM classics";
$result = mysql_query($query);

if (!$result) die ("Database access failed: " . mysql_error());
$rows = mysql_num_rows($result);

echo "";

for ($j = 0 ; $j < $rows ; ++$j)
{
$row = mysql_fetch_row($result);
echo "";
for ($k = 0 ; $k < 5 ; ++$k)
echo "";
echo "";
}

echo "

Author	Title	Category	Year	ISBN
$row[$k]

";

{
$author=get_post('author');
$title=get_post('title');
$category=get_post('category');
$year=get_post('year');
$isbn=get_post('isbn');

$query = "DELETE FROM classics VALUES" .
"('$author','$title','$category','$year','$isbn')";
if (!mysql_query($query, $db_server)) echo "DELETE failed: $query
" . mysql_error() ."
";
}

echo <<<_END

_END;

mysql_close($db_server);

function get_post($var)
{
return mysql_real_escape_string($_POST[$var]);
}

?>

The original test form that worked perfectly before I dissected it.
That form included the coding below:

require_once 'login_publications.php';

if(isset($_POST['delete']) && isset($_POST['isbn']))
{
$isbn = get_post('isbn');
$query = "DELETE FROM classics WHERE isbn='$isbn'";

if(!mysql_query($query, $db_server))
echo "DELETE failed: $query
" .
mysql_error() . "

";
}

if(isset($_POST['author']) &&
isset($_POST['title']) &&
isset($_POST['category']) &&
isset($_POST['year']) &&
isset($_POST['isbn']))

{
$author=get_post('author');
$title=get_post('title');
$category=get_post('category');
$year=get_post('year');
$isbn=get_post('isbn');

$query= "INSERT INTO classics VALUES" .
"('$author','$title','$category','$year','$isbn')";

if(!mysql_query($query, $db_server))
echo "INSERT failed: $query
" .
mysql_error() . "

";
}

echo <<<_END



  Author 


   Title 


Category 


    Year 


    ISBN

_END;

$query= "SELECT * FROM classics";
$result=mysql_query($query);

if(!$result) die ("Database access failed: " . mysql_error());
$rows=mysql_num_rows($result);

for($a = 0 ; $a < $rows ; ++$a)
{
$row=mysql_fetch_row($result);
echo <<<_END



  Author $row[0]


   Title $row[1]


Category $row[2]


	Year $row[3]


	ISBN $row[4]

_END;
}

mysql_close($db_server);

function get_post($var)
{
return mysql_real_escape_string($_POST[$var]);
}

?>

Trouble displaying information from table in PHP, please help. (2 replies)

Costa Gauci — Mon, 29 Apr 2013 15:50:15 +0000

Hi there,

I'm learning PHP and have come across a problem I'm wondering if one of you can help me with.

I'm trying to run this code from Addison Wesley PHP and MySQL Web Development 4th Edn. When I try to run it, it comes up with a connection error but when I remove 'books' from line 22 it connects but doesn't seem to display any search results.

The code can be found here:

http://pastebin.com/diff.php?i=b7Zcez2g

Since then I have tried improving on the way I connect to the code but still receive no results, the new code can be found here:

http://pastebin.com/diff.php?i=b7Zcez2g

I am still very new to PHP and MySQL so would greatly appreciate any help given.

The information is definitely found in the database as shown here:

And the live docs can be found here:

http://www.uncensoreddesign.co.uk/php/book-o-rama/search.html

Many thanks,

Costa

Records missing after insert (3 replies)

Paul Nair — Fri, 19 Apr 2013 20:51:30 +0000

I have a problem with data not inserting into my database. The application uses PHP's PDO to connect to a MySQL version 5.5.27 server hosted by AWS.

One of my scripts does some queries, including 3 inserts. The first insert is fine, but for the second two inserts, no data appears in my table. These queries are both within functions that are called from various other scripts with no problems. I have my PDO object set to throw exceptions but no exceptions are thrown.

The tables I am inserting into both have auto-increment fields, and I am using PDO's lastInsertId method to get the inserted value from these fields, and it does give me a value. When I check the tables, the auto-increment value of those tables has increased by one, but there is no record under the auto-increment value given to me.

There is nothing in the MySQL error log, but when I look in the general log, I can see the insert statements. When I copy and paste them into the MySQL on the console, they run fine and the data is inserted. I've double checked that my application hasn't started a transaction by called the inTransaction method of the PDO object just before one of the inserts and it came back false. I've set the max_prepared_stmt_count to 1000 in case I was hitting a limit there but that hasn't helped.

After trying lots of things, the only way I've managed to get it to work is before running these two queries, I destroy my PDO object then recreate it. So I guess it's something to do with the code that runs before it doing something to my PDO object, but what I don't understand is how MySQL can take two inserts, not produce any errors, give back an auto increment id and increase the auto-increment value of the table, but not actually insert any data!

All tables are InnoDB and the php is version 5.3.10 running on Ubuntu. Any help on this would be greatly appreciated as I've run out of things to try to solve this now.

Adding and removing slashes (1 reply)

Matt Camill — Thu, 18 Apr 2013 23:15:31 +0000

Hi I’m in a class being taught PHP and MySql, and I’m posting this question more or less for clarification as to when and how you would use functions that add and remove slashes when inserting/retrieving data from the database. I can’t seem to find solid guidance with my current resources, so let me tell you what I’ve gathered so far, and please either correct me or expand on it so I can get the whole picture.

The best function to use for adding slashes to a record being inserted into a MySql database is mysqli_real_escape_string(). This much is simple and clear, but my big question here, is do you use this function only for data you intend to store as a string, or for all types including numeric?

Second question, we’re being taught to use floatval() on floating point numbers before we insert them into the database. Should we use the corresponding function for integer types as well? Anything else I need to know in regards to storing numeric types in a MySql database?

And lastly, my third major question, is the generic stripslashes() function good to remove slashes from data that you query, or is there a MySql specific one I should use instead? It shouldn’t make a difference I would think in this case, since it just removes the slashes for the purpose of your query. It doesn’t change the actual record in the database, right?

Any help on clearing all this up, so I can go about doing things the right way before I form any bad habits, would be immensely appreciated :)

pictures from database (1 reply)

Justyna Cala — Wed, 17 Apr 2013 13:39:05 +0000

So I've got images' path stored in my database for each object and I want to display the images on my webpage. I though i could do it for each element with following code:
$con=mysqli_connect("x","x","x","x");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT imagesource FROM staff WHERE id=1");
echo '';

mysqli_close($con);
?>

shows only the end of the code (no replies)

Justyna Cala — Sun, 14 Apr 2013 14:19:25 +0000

Hello

My code is:

$con=mysqli_connect("localhost","root","","images");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM images");

while($row = mysqli_fetch_array($result))
{
echo $row['name'] . " " . $row['source'];
echo "
";
}

mysqli_close($con);
?>

It doesn't display anything on the website except for the ending of php: "; } mysqli_close($con); ?>

Select data just after insert (5 replies)

Sophokles Zafeiris — Thu, 18 Apr 2013 02:42:52 +0000

Hi all,

I' m developing a php /mysql application.

I need to run a select query on a table just after an insert to the same table.

The reason is that the business owner wants to have the user to able to create a record in the table and see almost immideately the refreshed data in a select form object.

My problem is that the applicaion one time works and the other it does not.

I was using INNODB tables and i tried to convert them to MyISAM but with no result (i used an alter stetement)

Does anyone has an idea on my problem?

Thnka in advance

Query is not running Fastly (1 reply)

Nagarajan B — Sat, 13 Apr 2013 04:29:58 +0000

Hi,

I've around 5.00,000 Records in my Table. Now return a query its not running fastly. Below is the Query. I need to optimize below query for fetching faster records.

SELECT `id` AS company_id, `Company_name`, `City`, `State`, `Country`, `cleaned`, update_user, `date_modified` AS cleaned_date, `delete` FROM data_table_new WHERE date_modified = '2013-04-11' AND MATCH(Country) AGAINST ('India' IN BOOLEAN MODE) AND `cleaned` = 1 AND `delete` = 0 AND `junk` = 0 ORDER BY date_modified DESC;

Table Structure:
CREATE TABLE IF NOT EXISTS `data_table_new` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Company_name` text COLLATE utf8_unicode_ci,
`Short_Profile` text COLLATE utf8_unicode_ci,
`Telephone` text COLLATE utf8_unicode_ci,
`Alt_Telephone` text COLLATE utf8_unicode_ci NOT NULL,
`Alt_Telephone2` text COLLATE utf8_unicode_ci NOT NULL,
`Fax` text COLLATE utf8_unicode_ci,
`Alt_Fax` text COLLATE utf8_unicode_ci NOT NULL,
`Mobile` text COLLATE utf8_unicode_ci,
`Alt_Mobile` text COLLATE utf8_unicode_ci NOT NULL,
`Email` text COLLATE utf8_unicode_ci,
`Alt_Email1` text COLLATE utf8_unicode_ci NOT NULL,
`Alt_Email2` text COLLATE utf8_unicode_ci NOT NULL,
`Website` text COLLATE utf8_unicode_ci,
`Website1` text COLLATE utf8_unicode_ci,
`Website2` text COLLATE utf8_unicode_ci NOT NULL,
`Website3` text COLLATE utf8_unicode_ci NOT NULL,
`Address` text COLLATE utf8_unicode_ci,
`Address2` text COLLATE utf8_unicode_ci NOT NULL,
`Gender` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
`Contact_Person_FName` text COLLATE utf8_unicode_ci,
`Contact_Person_LName` text COLLATE utf8_unicode_ci NOT NULL,
`Posting` text COLLATE utf8_unicode_ci,
`City` text COLLATE utf8_unicode_ci,
`Zipcode` text COLLATE utf8_unicode_ci,
`Business_Type` text COLLATE utf8_unicode_ci,
`Year_of_Establishment` text COLLATE utf8_unicode_ci,
`Office_Size` text COLLATE utf8_unicode_ci NOT NULL,
`Legal_Status_of_Firm` text COLLATE utf8_unicode_ci,
`Certification` text COLLATE utf8_unicode_ci,
`Annual_Turnover` text COLLATE utf8_unicode_ci,
`Number_of_Employees` text COLLATE utf8_unicode_ci,
`Export_Percentage` text COLLATE utf8_unicode_ci,
`Main_Markets` text COLLATE utf8_unicode_ci,
`Legal_Owner` text COLLATE utf8_unicode_ci NOT NULL,
`Legal_Representative` text COLLATE utf8_unicode_ci,
`State` text COLLATE utf8_unicode_ci,
`Country` text COLLATE utf8_unicode_ci,
`Banker` text COLLATE utf8_unicode_ci,
`Income_Tax_Registration_No` text COLLATE utf8_unicode_ci,
`Central_Sales_Tax_No` text COLLATE utf8_unicode_ci,
`State_Sales_Tax_No` text COLLATE utf8_unicode_ci,
`Import_Export_Code` text COLLATE utf8_unicode_ci,
`Tax_Identification_No` text COLLATE utf8_unicode_ci,
`SSI_No` text COLLATE utf8_unicode_ci,
`CIN_No` text COLLATE utf8_unicode_ci,
`SCT_No` text COLLATE utf8_unicode_ci,
`RBI_No` text COLLATE utf8_unicode_ci,
`PAN_No` text COLLATE utf8_unicode_ci,
`TAN_No` text COLLATE utf8_unicode_ci,
`EPF_No` text COLLATE utf8_unicode_ci,
`ESI_No` text COLLATE utf8_unicode_ci,
`Service_Tax_Registration_No` text COLLATE utf8_unicode_ci,
`Excise_Registration_No` text COLLATE utf8_unicode_ci,
`Dun_Bradstreet_Number` text COLLATE utf8_unicode_ci,
`Value_Added_Tax_Registration_No` text COLLATE utf8_unicode_ci,
`Industry` text COLLATE utf8_unicode_ci,
`Key_Customers` text COLLATE utf8_unicode_ci,
`DGFT_IE_Code` text COLLATE utf8_unicode_ci,
`Registration_Authority` text COLLATE utf8_unicode_ci,
`Payment_Mode` text COLLATE utf8_unicode_ci,
`Shipment_Mode` text COLLATE utf8_unicode_ci,
`Registration_No` text COLLATE utf8_unicode_ci,
`URL_ID` text COLLATE utf8_unicode_ci,
`Company_Introduction` text COLLATE utf8_unicode_ci,
`Detailed_Type` text COLLATE utf8_unicode_ci NOT NULL,
`Detailed_Profile` text COLLATE utf8_unicode_ci NOT NULL,
`Company_Profile` text COLLATE utf8_unicode_ci,
`Products_Manufacturing_and_Exporting` text COLLATE utf8_unicode_ci,
`Company_Branches` text COLLATE utf8_unicode_ci,
`Services` text COLLATE utf8_unicode_ci,
`Member_Affiliates` text COLLATE utf8_unicode_ci,
`We_Sell` text COLLATE utf8_unicode_ci,
`We_Buy` text COLLATE utf8_unicode_ci,
`Brands` text COLLATE utf8_unicode_ci,
`Factory_Size` text COLLATE utf8_unicode_ci,
`Factory_Location` text COLLATE utf8_unicode_ci,
`QA_QC` text COLLATE utf8_unicode_ci,
`Number_of_Production_Lines` text COLLATE utf8_unicode_ci,
`Number_of_RND_Staff` text COLLATE utf8_unicode_ci,
`Number_of_QC_Staff` text COLLATE utf8_unicode_ci,
`Contract_Manufacturing` text COLLATE utf8_unicode_ci,
`Registered_Location` text COLLATE utf8_unicode_ci NOT NULL,
`Registered_Address` text COLLATE utf8_unicode_ci,
`cleaned` tinyint(1) NOT NULL,
`date_modified` date NOT NULL,
`approved_date` date NOT NULL,
`delete` tinyint(1) NOT NULL,
`junk` tinyint(1) NOT NULL,
`admin_user` int(4) NOT NULL,
`update_user` bigint(2) NOT NULL,
`cr_date` datetime NOT NULL,
`buyer_supplier` tinyint(2) NOT NULL COMMENT '0-Supplier,1-Buyer,2-Both',
`company_represent` tinyint(2) NOT NULL DEFAULT '0' COMMENT '0-Yes,1-No',
PRIMARY KEY (`id`),
KEY `search_string` (`Company_name`(10),`Website1`(30),`City`(10),`State`(10),`Country`(10),`cleaned`,`delete`,`junk`,`date_modified`,`approved_date`,`admin_user`),
FULLTEXT KEY `ft_website1` (`Website1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

Mysql Login systeem (no replies)

Henk Jongebroek — Wed, 10 Apr 2013 17:41:09 +0000

// login form

Member Login
Naam	:
Email	:

//einde login form

//login check

$host="";
$username="";
$password="";
$db_name="";

mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$naam=$_POST['naam'];
$email=$_POST['email'];

// To protect MySQL injection (more detail about MySQL injection)
$naam = stripslashes($naam);
$email = stripslashes($email);
$naam = mysql_real_escape_string($naam);
$email = mysql_real_escape_string($email);

$sql="SELECT * FROM gebruikers WHERE naam='$naam' and email='$email'";
$result=mysql_query($sql);

$count=mysql_num_rows($result);

if($count==1){

session_register("naam");
session_register("email");
header("location:login_succes.php");
}
else {
echo "Wrong Username or Password";
}
?>

//einde login check

//login succes

session_start();

if(!session_is_registered(naam)) {
echo 'U heeft hier niks te zoeken!!';
}
else {

?>

Welkom,

}
?>

//einde login succes

//registreer form

Member registreer
Naam	:
Email	:
Wachtwoord	:
Herhaal wachtwoord	:

//einde registreer form

//registreer check

$host="";
$username="";
$password="";
$db_name="";

mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$naam = $_REQUEST['naam'];
$email = $_REQUEST['email'];
$ww = $_REQUEST['ww'];
$ww2 = $_REQUEST['ww2'];
$date = $_REQUEST['date'];

if(empty($naam)){
echo 'U moet een naam invoeren
';
}
if(empty($email)){
echo 'U moet een email invoeren
';
}
if(empty($ww)){
echo 'U moet een wachtwoord invoeren
';
}
if($ww != $ww2){
echo 'Het wachtwoord is niet goed herhaald!';
}
else{
$insert = mysql_query("INSERT INTO gebruikers (naam, email, wachtwoord, datum) VALUES ('$naam', '$email', '$ww', '$date')");

if(!$insert){
die("There's little problem: ".mysql_error());
}
else{
echo 'U ben geregistreerd!';
}
}
?>

//einde registreer check

I hope this will help you. Reply for help

MySql Query Help (2 replies)

Alan Lynch — Sat, 30 Mar 2013 12:20:39 +0000

Hi professionals.

i am new to php and mysql and I am trying to query my database to calculate the distance with the help from google instructions on this page.

https :// developers.google.com/maps/articles/phpsqlsearch_v3

when I try to query my database with the following query as advised by them no results are returned. here is my query, I think it is because of the order of exectuion of sql statement and it does not recognise the "distance" column name

SELECT suburb, state, latitude, longtitude, ( 6371 * acos( cos( radians(153) ) * cos( radians( latitude ) ) * cos( radians( longtitude ) - radians(-27) ) + sin( radians(153) ) * sin( radians( latitude ) ) ) ) AS distance FROM Postcode HAVING distance < 25 ORDER BY distance

any ideas as I really need the distance < 25 part so users can search by distance

thanks

Trouble Connecting to Database via PHP (14 replies)

Louis Andreou — Fri, 29 Mar 2013 16:13:31 +0000

Ok so I've got a database with a populated table (just to practice for now)

I'm trying to connect to my mysql database via PHP and have found the code to help me to do just that, but when I try I get this error message on my browser:

Fatal error: Call to undefined function mysqli_connect() in C:\Apache24\htdocs\mysql_connect.php on line 14

I have looked at the specs of the PHP build I have installed and this is the configure command:

cscript /nologo configure.js "--enable-snapshot-build" "--disable-isapi" "--enable-debug-pack" "--without-mssql" "--without-pdo-mssql" "--without-pi3web" "--with-pdo-oci=C:\php-sdk\oracle\instantclient10\sdk,shared" "--with-oci8=C:\php-sdk\oracle\instantclient10\sdk,shared" "--with-oci8-11g=C:\php-sdk\oracle\instantclient11\sdk,shared" "--enable-object-out-dir=../obj/" "--enable-com-dotnet=shared" "--with-mcrypt=static" "--disable-static-analyze" "--with-pgo"

I did install the latest PHP 5.4.12, should this work automatically?

php-mysql help. Need to create a dynamic url based on the table value (2 replies)

Simon Mandy — Tue, 26 Mar 2013 14:22:52 +0000

I have a database named 'counter' and a table named 'info'. which stores the information of client ip address (ip_address), client brower details (user_agent) and date and time visited my website (datetime).

I have a php webpage which displays above information in a table format code is below,

// ########################
// ### Display visitors ###
// ########################

echo '

Visitors

' ;

$result = mysql_query("SELECT * FROM $dbtableinfo ORDER BY id DESC")
or die(mysql_error());

echo "";
echo '';

// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result ))
{
// Print out the contents of each row into a table
echo '";
}
echo "" ;
echo "

IP	User agent	Date & Time
'; echo $row['ip_address']; echo '	'; echo $row['user_agent']; echo '	'; echo $row['datetime']; echo "
Total unique IP´s	$totalips

";

?>

First coulmn in the web page is the ip address of the client. Now I would like to make that as a hyperlink with this url http://iplocation.truevue.org/.html

Can someone assist?

How to wait for INSERT to happen before moving onto next INSERT query? (5 replies)

Mike Liedike — Sun, 17 Mar 2013 01:18:23 +0000

I am inserting data into, table1. After this data is inserted I am trying to insert data into table2 to based off table1 data. Table2 has FK from table one that I am referencing. This is how I have my inserts setup up right now (this does not work). Any help on how to clean this up and make it work correctly would be greatly appreciated.

$r = mysqli_query($dbc, "INSERT INTO player(gamedate, home, away, goalsfor, goalsagainst, yellowcards, redcards) VALUES ('$gamedate','$home','$away','$goalsfor','$goalsagainst','$yellowcards','$redcards')");
if (mysqli_affected_rows($dbc) == 1) {
	echo "Added game successfully";
	$playerid = mysql_insert_id();
	$gameresult = mysqli_query($dbc, "INSERT INTO game(player_id, pposition, pgoalsfor, pgoalsagainst, psog, ppk, pyellowcards, predcards) VALUES ('$playerid','$pposition','$pgoalsfor','$pgoalsagainst','psog','ppk','pyellowcards','predcards')");
	if (mysqli_affected_rows($dbc) == 1) {
		echo "Added Individual Stats successfully";
		mysqli_close();
	}
	else {
		echo "There was an error.  Your stats were not added.";	
		mysqli_close();
	}
	mysqli_close();
}

else {
	echo "There was an error.  Your game was not added.";	
	mysqli_close();
}

Thank you,
Mike

Inserting records with mysqli_insert_id (1 reply)

Mike Grunow — Fri, 15 Mar 2013 14:15:12 +0000

I am trying to get data from a csv file and into a mysql database. My objective for example is to get the first few records and add them to a table then get the primary id as well as a few other rcords and add them to a different table and so on.
Th ecode I have so far works but it is buggy and inserts blank rows apparently randomly. Any ideas why? Also this is just sample data in the real app I would have more data and more tables to populate.

$link = mysqli_connect("localhost", "user", "pass", "test");

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error()); exit();}

$objCSV = fopen("registerSales.csv", "r"); //get data from csv file
while (($objArr = fgetcsv($objCSV, 10000, ",")) !== FALSE) {
$strSQL = "INSERT INTO sample ";
$strSQL .= "(product_id,price,sku_id) ";
$strSQL .= "VALUES ";
$strSQL .= "('".$objArr[0]."','".$objArr[1]."','".$objArr[2]."') ";
echo "$strSQL";
//$strSQL .=",'".$objArr[3]."','".$objArr[4]."','".$objArr[5]."') ";
$objQuery = mysqli_query($link, $strSQL);
$i = mysqli_insert_id($link);

//rerun block with new id val
$strSQL = "INSERT INTO table2 ";
$strSQL .= "(row1, row2)"; //"(product_desc,product_sku_id) ";
$strSQL .= "VALUES ";
$strSQL .= "('".$i."','".$objArr[1]."') ";
$objQuery = mysqli_query($link, $strSQL);
echo "$strSQL" . "
";
} //end while
fclose($objCSV);
?>

mysql_fetch_assoc(): supplied argument is not a valid MySQL error (3 replies)

niek h — Tue, 12 Mar 2013 14:23:50 +0000

hallo,

if i try do run the code below i get this error: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ....
What could I to do make my code work?
Thanks for all your effort already :).

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">;

Voorbeeld 9.4 - De resultaten naar het scherm schrijven met mysql_fetch_row()
$server = "localhost"; // naam van de MySQL-databaseserver
$user = "root"; // inlognaam
$wachtwoord = "usbw"; //wachtwoord voor dit account
$database = "ict"; // naam van de database die we gebruiken
$query = mysql_query("SELECT 'naam_toneelstuk','Gerne' FROM 'table 2' WHERE 'Gerne' = 'toneel'"); // de SQL-query die wordt uitgevoerd

while ($row = mysql_fetch_assoc ($query) ) {

echo $row['gerne'];

echo $row['naam_toneelstuk'];
}

?>

niek

How to set Dynamic WHERE CONDITIONS to show customer rows depending on Login using PHP? (3 replies)

Mrkey Lee — Wed, 13 Mar 2013 16:16:00 +0000

I have two tables in MYSQL, tblLogin and tblCustomers with the following Columns: tblLogin{LoginID as Primary_Key, UserName, Password} and tblCustomer{Customer_ID as Primary_Key, Customer_FullName, OrderID, OrderDetails, submission_date, Customer_LoginID as Foreign_key}.
What I want is to let customers to view their own records only due to their login information.
To accomplish this I set two forms namely Login_Form.php and Results.php where after pressing SUBMIT BUTTON of Login_Form.php the user should be able to see his own records displayed on Results.php form dynamically.
Here is my code for these forms!
1. Login_form.php
//Begin Login_Form.php

http-equiv="content-type">

//End Login_Form.php

2. Results.php
//Begin results.php
$dbhost = 'localhost:3306';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'Customer_ID, Customer_FullName, OrderID, OrderDetails,submission_date, Customer_LoginID WHERE {...YOUR HELP IS NEEDED PLEASE....?}';

mysql_select_db('tblCustomers');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
echo "Customer_ID :{$row['Customer_ID']}
".
"Full Name: {$row['Customer_FullName']}
".
"Order Details: {$row['OrderDetails']}
".
"Date : {$row['submission_date']}
".
"--------------------------------
";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>
//End results.php

Now how to fetch specific data due to specific customer?
Any hint would be highly appreciated
Please!!!!

MM_Username insert into field (1 reply)

James Halliwell — Sat, 09 Mar 2013 17:19:30 +0000

Hi everyone,

new to php and Mysql, moved over from ms access, i am creating a website to help people monitor there requests, they login with a login name and password like

login 'James Helliwell',
password ABCDEF1

i have a form so they enter there details in but i want it to insert the login name (MM_Username) into a filed called Account Manager

i think its the below code from my site that i need to modify, teaching myself so any help would be grealty appreciated

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form2")) {
$insertSQL = sprintf("INSERT INTO RequestsMaster (FieldSalesManager, AccountManager, AccountName, RequestMonth, `Module`, ModuleDescription, OrderQty, ApprovedByFSM, PotentialExtraBusiness, FinalApproval, ShippedStatus, ShippedDate) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['FieldSalesManager'], "text"),
GetSQLValueString($_POST['AccountManager'], "text"),
GetSQLValueString($_POST['AccountName'], "int"),
GetSQLValueString($_POST['RequestMonth'], "date"),
GetSQLValueString($_POST['Module'], "text"),
GetSQLValueString($_POST['ModuleDescription'], "text"),
GetSQLValueString($_POST['OrderQty'], "int"),
GetSQLValueString($_POST['ApprovedByFSM'], "int"),
GetSQLValueString($_POST['PotentialExtraBusiness'], "text"),
GetSQLValueString($_POST['FinalApproval'], "int"),
GetSQLValueString($_POST['ShippedStatus'], "text"),
GetSQLValueString($_POST['ShippedDate'], "date"));

mysql_select_db($database_DePuyConnection, $DePuyConnection);
$Result1 = mysql_query($insertSQL, $DePuyConnection) or die(mysql_error());

$insertGoTo = "Directory.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}

select and if statement (1 reply)

Hassan Ahmed — Sat, 09 Mar 2013 17:26:11 +0000

al salam alikum =)

guys i want to know how to fix a complicated query

i have two tables
subsystem -> 1st table
topics -> 2nd table

subsystem :
subid
userid
type
typeid ;

topics :
topicid
title
catid
userid ;

and i got 3 records ( for example ) in the subsystem table like thie
1 - 1 - user - 1
2 - 1 - user - 2
3 - 1 - cat - 1

and i wanna to fetch the topics from topics table like this

query > select * from topics inner join subsystem on topics.userid=subsystem.userid where ( and here is the problem ! i want to fetch the topics where subsystem.typeid=userid "if subsystem.type=user" and where subsystem.typeid=catid "if subsystem.type=cat")

can anybody help !

and thnx =)

Connecting to a database and displaying 1 result (2 replies)

Chris Harris — Sat, 09 Mar 2013 09:32:59 +0000

I have been working on a code to establish conenction to a database to to retrieve the information on the database. The database contains a list of names and a colour code next to them.

I have connected to the database and can pull the information but it does not limit the number of rows that is being returned (it shows all the entries).

Can anyone look at my code and help me with something that is clearly missing???!?! The FirstName entries are all unique so I only need to check the name with the color code.

$con=mysqli_connect("localhost","####","####","####");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM Members");

while($row = mysqli_fetch_array($result))
{
echo $row['FirstName'] . " " . $row['Level'];
echo "
";
}

mysqli_close($con);
?>