Still looking for a solution for my INSERT

PHP
1

Hi,
I’m bumping this up as I really need a solution
The relevant code is below. The programme runs ok, no warnings or errors. The newly created table keywordbook is loaded but only with the booknumber, keywordid remains empty. I have tried all manner of ways of coding the source field but to no avail. The echo just confirms what’s happened. The number or records is correct but where or how do I find that field?
Thanks
Mike
PS I’ve removed the error statements for clarity.


$result0 = mysqli_query
($link, 'SELECT booknumber FROM keywords
          INNER JOIN keywordtab 
          ON keywords.keyword = keywordtab.keyword');
  WHILE ($row = mysqli_fetch_array($result0))

  {
    echo $keywordid," - ",$row['booknumber'],"<br />";
    $sql = 'INSERT INTO keywordbook SET
         keywordid  = "' . keywordtab.keywordid . '",
         booknumber = "' . $row['booknumber'] . '"';
  };
2

$keywordid is never defined, so I suppose you meant $row[‘keywordid’] , assuming ‘keywordid’ is a column in one of the two tables

keywordtab.keywordid does not have a $ in front of it, so it’s not a variable, but will be treated as a constant, or -if it is not defined as a constant- a string. However, I think you want the value of the column ‘keywordid’ in the keywordtab table, in which case it should simply be $row[‘keywordid’]

TL;DR Both should be $row[‘keywordid’] IMHO

3

Modtup,

My thanks, you solved it. For other readers I hadn’t realised that the SELECT should also select the relevant field from the JOINed/2nd table.

What a relief.

Mike Hayes

4

Keywords

  • keyword
  • booknumber

keywordtab

  • tab_keywordid
  • tab_keyword

Here’s the third table.

$sql = 'CREATE TABLE keywordbook
  (
   keywordid   INT(11) NOT NULL,
   booknumber INT(6)  NOT NULL
  )';

Booknumber is taken from keywords,
keywordid is taken from keywordtab

That’s all the info.

The last suggested coding from modtup didn’t work.

Mike

5

try below…


$result0 = mysqli_query($link,'SELECT booknumber,tab_keywordid
							   FROM keywords
		  					   INNER JOIN keywordtab 
		  					   ON keywords.keyword = keywordtab.keyword');
  
WHILE ($row = mysqli_fetch_array($result0)) {
	echo $row['tab_keywordid']," - ",$row['booknumber'],"<br />";
	$sql = 'INSERT INTO keywordbook
			SET
		 	tab_keywordid  = "'.$row['tab_keywordid'].'",
		 	booknumber = "'.$row['booknumber'].'"';
};
6

The tables are:

Keywords

  • keyword
  • booknumber

keywordtab

  • tab_keywordid
  • tab_keyword

see attachments for content.

7

If the keywordid is in the keywordtab table, which I’m not sure of because I haven’t seen your tables, you should change your query to

SELECT
   booknumber
 , keywordid
FROM
   keywords
      INNER JOIN
   keywordtab 
      ON
   keywords.keyword = keywordtab.keyword

If that doesn’t work, could you post the output SHOW CREATE TABLE for both tables (keywords and keywordtab) ?

8

I get booknumber ok which is in the row but where is the info from the second - keywordtab - table? I tried row but keywordid doesn’t exist there. The output fields all remain empty, zero.
What now?

Thanks for your time.

9

The errors you’ve marked; what’s wrong with them?
Thanks.

10 

$result0 = mysqli_query
($link, 'SELECT booknumber FROM keywords
          INNER JOIN keywordtab 
          ON keywords.keyword = keywordtab.keyword');
  WHILE ($row = mysqli_fetch_array($result0))

  {
    echo [COLOR="Red"]$keywordid[/COLOR]," - ",$row['booknumber'],"<br />";
    $sql = 'INSERT INTO keywordbook SET
         keywordid  = "' . [COLOR="Red"]keywordtab.keywordid[/COLOR] . '",
         booknumber = "' . $row['booknumber'] . '"';
  };

As far as I can see the error is in the parts I made red.

Assuming that $link is a valid mysqli link resource that is.
And please take a look at the mysqli_real_escape_string() function.

:slight_smile:

11

Ok this should now works based on the 3 tables given…


$result0 = mysqli_query(
	$link,
	'SELECT
		booknumber,
		tab_keywordid
	FROM keywords
	INNER JOIN keywordtab 
	ON keywords.keyword = keywordtab.tab_keyword');

WHILE ($row = mysqli_fetch_array($result0)) {
	echo $row['tab_keywordid']," - ",$row['booknumber'],"<br />";
	$sql = 'INSERT INTO
				keywordbook
			SET
			 	keywordid  = "'.$row['tab_keywordid'].'",
				booknumber = "'.$row['booknumber'].'"';
};