Solved

help in php snippet code;insert into table2, ids from table1

Posted on 2014-04-03
8
414 Views
Last Modified: 2014-04-04
hello i would like to improve my code i have two textareas one called texte and a other called keyword
what the script would do is as long as there are keywords insert it to a table `mots_clefs (rows: id, id is a autoincrement number ,Nom) then insert data into a table citation  (`id`, `auteur`, `texte`, `mots_clefs`), with  in mots_clefs the value of the ids inserted in keywords

table mot_clefs

CREATE TABLE IF NOT EXISTS `mots_clefs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Nom` varchar(80) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

Open in new window


table citations

CREATE TABLE IF NOT EXISTS `citations` (
  `id` int(11) unsigned NOT NULL,
  `auteur` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `texte` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `mots_clefs` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Open in new window




my uncompleted test
$mysqli = new mysqli('localhost', 'root', '', 'citations');


$kw = filter_input(INPUT_POST, 'keyword');
$citation = filter_input(INPUT_POST, 'texte');
if ($kw)
{
	$keywords = explode(',', $kw);
	$ids = array();
	foreach ($keywords as $k)
	{
		if (!empty($k))
		{
			$query = "INSERT INTO mots_clefs (Nom) VALUES('$k')";
			if ($result = $mysqli->query($query) or die("Error: " - mysqli_error()))
				array_push($ids, $mysqli->insert_id);
		}
	}
	$query = "INSERT INTO `citations` (`id`, `auteur`, `texte`, `mots_clefs`) VALUES
(1, '1', 'À elle seule,La vie est une citation.', '$kw')";

	
}

Open in new window


thanks
explain2.jpg
0
Comment
Question by:jerrrrry
  • 5
  • 3
8 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39976279
Notice how you used explode() to break the comma-separated list of keywords apart (line 8)?  PHP has implode() to turn an array into a comma-separated string.  So maybe you want to add this after line 18:
$mots_clefs = implode(',', $ids);

Open in new window

And change line 20 to this:
(1, '1', 'À elle seule,La vie est une citation.', '$mots_clefs')";

Open in new window

And then, of course, you'll want to run that query you created in the $query variable.

I still think you would benefit greatly from taking some time to study PHP instead of just trying to copy code you found on the internet.  In a couple of months of structured study and practice you could put yourself a couple of years ahead of the trial-and-error path.

You may also want to learn how to use the MySQLi Class.  This article has good examples for the most common uses.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

If you're familiar with MySQL and you're moving to MySQLi, this page on my site has a function mapping across the extensions.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Not sure exactly where you're going with this application (it almost looks like schoolwork) but you might want to consult a data base expert about the design of the citations table.  When I see a column that contains more than one data point, I find myself wondering if that's a design that makes sense.
0
 

Author Comment

by:jerrrrry
ID: 39976361
it doesn't work now
the insert into citation query is not done ?

i don't understand this  
When I see a column that contains more than one data point, I find myself wondering if that's a design that makes sense.

can u explain me what is more than one data point whit a example please, i really don't understand what u mean, tks
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39976526
We can't write your application for you, so if it does not work you will need to show us the URL link to the web page that has the script installed.  Then we can see the error messages.  And if you can show us the exact code that is running at that URL, we can line up the error messages with the failing lines of code.  In this way we can probably provide more concrete help.

An example of a column with more than one data point is exactly what you have in the table citations and the column mots_clefs

Seriously, I think you will be so much better off if you take some time to work through a structured program of learning, such as those indicated in this article.  You've got a lot of moving parts here and I can tell you don't understand most of them.  There is no shame in ignorance -- we were all new to these technologies once and we all had to learn the basics first.  This should be easier for you and if you get a foundation in how PHP works, then build upon that foundation, you will get better results, I promise.
0
 

Author Comment

by:jerrrrry
ID: 39976570
<?php
$mysqli = new mysqli('localhost', 'root', '', 'citations');


$kw = filter_input(INPUT_POST, 'keyword');
$citation = filter_input(INPUT_POST, 'texte');
if ($kw)
{
	$keywords = explode(',', $kw);
	$ids = array();
	foreach ($keywords as $k)
	{
		if (!empty($k))
		{
			$query = "INSERT INTO mots_clefs (Nom) VALUES('$k')";
			if ($result = $mysqli->query($query) or die("Error: " - mysqli_error()))
				array_push($ids, $mysqli->insert_id);
		}
	}
	$mots_clefs = implode(',', $ids);
	$query2 = "INSERT INTO `citations` (`id`, `auteur`, `texte`, `mots_clefs`) VALUES
(1, '1', 'À elle seule,La vie est une citation.', '$mots_clefs')";
$result2 = $mysqli->query($query2) or die("Error: " - mysqli_error());
                                            

	
}

Open in new window



Warning: mysqli_error() expects exactly 1 parameter, 0 given in C:\wamp\www\citations\test.php on line 23


i've begun to read your explaination on php...
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39976594
Great!  While you read, I'll look over this code snippet.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39976658
Let's see if this makes sense.  It will give you somewhat better error and warning visualization.  I tried to add comments that would help make sense of the changes.  Obviously this is untested code because I do not have your data model, but it should be fairly close to right, at least in principle.

I'm a little unsure of the table definition for citations.  I think you run the risk of a character set collision when you mix UTF-8 and Latin1.  This article explains what could happen and how to detect the symptoms.  European accented character are especially prone to trouble here.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11880-Unicode-PHP-and-Character-Collisions.html

<?php // demo/temp_jerrrrry.php

// WE RAISE THE ERROR REPORTING TO MAKE DEBUGGING EASIER
error_reporting(E_ALL);

// OBJECT-ORIENTED MYSQLI TO CONNECT AND SELECT
$mysqli = new mysqli('localhost', 'root', '', 'citations');

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// GET THE REQUEST VARIABLES
$kw = filter_input(INPUT_POST, 'keyword');
$citation = filter_input(INPUT_POST, 'texte');

// IF THERE IS ANYTHING IN THE REQUEST
if ($kw)
{
    // MAKE AN ARRAY OF KEYWORDS FROM THE COMMA-SEPARATED INPUT
    $keywords = explode(',', $kw);

    $ids = array();
    foreach ($keywords as $k)
    {
        // WITH EACH NON-EMPTY KEYWORD
        if (!empty($k))
        {
            // MAKE SURE THE VARIABLE IS SAFE FOR USE IN A QUERY
            $safe_k = $mysqli->real_escape_string($k);

            // RUN THE QUERY AND TEST FOR ERRORS
            $query  = "INSERT INTO mots_clefs (Nom) VALUES('$safe_k')";
            $result = $mysqli->query($query);

            // IF THERE IS AN ERROR, CONSTRUCT AN ERROR MESSAGE
            if (!$result)
            {
                $err
                = "QUERY FAIL: "
                . $query
                . ' ERRNO: '
                . mysql_errno()
                . ' ERROR: '
                . mysql_error()
                ;
                trigger_error($err, E_USER_ERROR);
            }

            // SAVE THE INSERT ID NUMBER
            $ids[] = $mysqli->insert_id;
        } // END IF !EMPTY
    } // END FOREACH $keywords

    // MAKE A STRING FROM THE ARRAY OF INSERT-IDs
    $mots_clefs = implode(',', $ids);

    // NOTE THAT WE DO NOT INSERT THE AUTO_INCREMENT KEY - SQL DOES THAT FOR US
    $query2  = "INSERT INTO `citations` (`auteur`, `texte`, `mots_clefs`) VALUES ('1', 'À elle seule,La vie est une citation.', '$mots_clefs')";
    $result2 = $mysqli->query($query2);
    if (!$result2)
    {
        $err
        = "QUERY FAIL: "
        . $query2
        . ' ERRNO: '
        . mysql_errno()
        . ' ERROR: '
        . mysql_error()
        ;
        trigger_error($err, E_USER_ERROR);
    }
}

// IF THERE IS NOTHING IN THE REQUEST
else
{
    trigger_error('NO INPUT DATA', E_USER_NOTICE);
}

Open in new window

0
 

Author Closing Comment

by:jerrrrry
ID: 39979084
you rock
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39979148
Thanks for using EE and thanks for your kind words!  Best, ~Ray
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This is an explanation of a simple data model to help parse a JSON feed
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now