Best Place to get help for a PHP MYSQL Beginner? Desperation.

[jimmyoneshot]

I'm really desperate for help in a couple of php/mysql queries and code that I need to implement and I was wondering where it would be best to get it, basic stuff? I am a flex student but need to populate my applications via php/mysql but my knowledge of these languages is very limted. I'm so desperate that I'm thinking of taking drastic measures. Can anybody please help me out? This is a serious cry for help here.

[litebearer]

do you have any code that you have tried that you can show us AND tell us where you are having problems?

[andrewgauger]

Is the problem getting started with the languages (PHP & MySQL) or the integration with Flash/Flex?

 

Are you able to do something like this:

http://cookbooks.adobe.com/index.cfm?event=showdetails&postId=11146

 

Is the problem "How to create a database and dynamically pull data from it to populate a Flash application?" or is it "How to make a PHP/MySQL project?" 

 

Would you also provide a few details of what type of data you want to store and what type of queries you are trying to pull?  Generally for a novice I recommend reading a book, especially if you have a good public library where you don't need to invest money to get started.

[jimmyoneshot]

Thanks for the replies the main code I'm trying to manage is the following.

 

I'm trying to select all of the values in the linkid column of my userlinks table for a specfic userid that gets submitted to the php file where the value of the favourited column is 'N'. Here's a screenshot of my userlinks table:-

 

http://i223.photobucket.com/albums/dd147/jimmyoneshot/userlinkstable.jpg

 

Then I'm trying to get all the records from all the columns in the links table in which the value linkid match these linkids. Here's my links table:-

 

http://i223.photobucket.com/albums/dd147/jimmyoneshot/linkstable.jpg

 

I'm pretty useless at php but I've been tweaking my code non stop and can't get it to output anything. Here's the code I've got so far. Can anyone please tell me where I'm going wrong:-

 

<?php 

header("Content-type: text/xml"); 

$host = ""; 
$user = ""; 
$pass = ""; 
$database = ""; 

$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host."); 
mysql_select_db($database, $linkID) or die("Could not find database."); 

$param_userid = $_POST["userid"];

$text = $param_userid;

   $query1 = "SELECT linkid FROM userlinks WHERE userid = '$param_userid' AND favourited = 'N'";
   $resultID1 = mysql_query($query1, $linkID) or die("Data not found."); 
    
   $query2 = "SELECT * FROM links WHERE linkid = '$resultID1' ORDER BY label ASC"; 
   $resultID2 = mysql_query($query2, $linkID) or die("Data not found."); 

   $xml_output = "<?xml version=\"1.0\"?>\n"; 
   $xml_output .= "<links>\n"; 

   for($x = 0 ; $x < mysql_num_rows($resultID2) ; $x++){ 
       $row = mysql_fetch_assoc($resultID2);
       $xml_output .= "\t<link>\n";
       $xml_output .= "\t\t<linkid>" . $row['linkid'] . "</linkid>\n";
       $xml_output .= "\t\t<categoryid>" . $row['categoryid'] . "</categoryid>\n"; 
       $xml_output .= "\t\t<label>" . $row['label'] . "</label>\n"; 
       $xml_output .= "\t\t<icon>" . $row['icon'] . "</icon>\n"; 
       $xml_output .= "\t\t<url>" . $row['url'] . "</url>\n";
         $xml_output .= "\t</link>\n"; 
    
   } 

   $xml_output .= "</links>"; 

   echo $xml_output; 

?> 

 

Edit by thorpe: We have

tags for good reason.

[jimmyoneshot]

The second query I'm having trouble with is an update/insert query. Basically each time a user of my system logs in their userid is sent to my php file ($param_userid) I then want this to insert all the linkids from the links table into the linkids of the userlinks table FOR that specific user id and ignore any linkids they may already have i.e. update them. Here's the query I have so far but it doesn't seem to work:-

$sql = "UPDATE userlinks SET userid, linkid SELECT '$param_userid', linkid FROM links WHERE userid = '$param_userid'";
$result = mysql_query($sql);
if (mysql_affected_rows($result) == 0) {
$sql = "INSERT INTO userlinks( userid, linkid ) SELECT '$param_userid', linkid FROM links";
$result = mysql_query($sql);
}

 

Edit by thorpe: And

tags too!

[andrewgauger]

$query1 = "SELECT linkid FROM userlinks WHERE userid = '$param_userid' AND favourited = 'N'";

  $resultID1 = mysql_query($query1, $linkID) or die("Data not found.");

   

  $query2 = "SELECT * FROM links WHERE linkid = '$resultID1' ORDER BY label ASC";

  $resultID2 = mysql_query($query2, $linkID) or die("Data not found.");

 

I would replace this with:

 

$query2="SELECT links.* FROM links LEFT JOIN userlinks ON (links.linkid=userlinks.linkid) WHERE userlinks.userid = '$param_userid' AND userlinks.favourited='N'"
$resultID2=mysql_query($query2, $linkID) or die("Data not found."); 

 

You see, you forgot to parse the $resultID (the first one) into a usable format (such as using mysql_fetch_assoc).  When you tried to query using the variable $resultID1 you were putting a MySQL resource value (which is not parse-able as query-data).  I decided to optimize your queries, because it is better to solve MySQL queries with one lookup to the database.  You really were on the right track though!

 

#2

You have to put subqueries in parenthesis.  I'm not sure that you need both sets, but you do need one to separate values, and one to surround subqueries, so I don't think it will hurt.

 

$sql = "UPDATE userlinks SET linkid = (SELECT linkid FROM links WHERE userid = '$param_userid) WHERE userid='$param_userid'";
$sql = "INSERT INTO userlinks( userid, linkid ) VALUES ((SELECT '$param_userid', linkid FROM links))";

 

One more thing.  If these have errors, or in some way do not work, please put at the top of your script:

ini_set('display_errors', 1);

 

And after every $variable=mysql_query(...)

echo mysql_error();

 

Please post any debugging and errors output in future posts.

 

Thanks for trying out PHP and MySQL!

 

[andrewgauger]

I had handled this through PM but wanted to post the result because it was pretty unique.

 

There are two columns that should provide "uniqueness" in the table (userlinks): userid, linkid

So instead of using:

$sql = "UPDATE userlinks SET userid, linkid SELECT '$param_userid', linkid FROM links WHERE userid = '$param_userid'";

$result = mysql_query($sql);

if (mysql_affected_rows($result) == 0) {

$sql = "INSERT INTO userlinks( userid, linkid ) SELECT '$param_userid', linkid FROM links";

$result = mysql_query($sql);

}

 

This doesn't work because you cannot use Update to modify multiple rows with different values from a subquery.  However, if you first apply:

 

ALTER TABLE userlinks ADD UNIQUE (userid, linkid);

 

You can:

 

REPLACE INTO userlinks (userid,linkid) VALUES ((SELECT '$param_userid' , linkid FROM links));

 

So I thought I should post this because I had never worked with a scenario where 2 columns TOGETHER define uniqueness.

[jimmyoneshot]

It is pretty strange. I see what you're saying with the coupling of the userid and linkid as unique values in the userlinks column. That makes sense as each userid will only have one of each linkid applied to it. I've done that and I've changed my code to this:-

 

<?php

 

$host = "";

$username = "";

$password = "";

$db_name = "";

 

$mysql_connection = mysql_connect($host, $username, $password);

 

mysql_select_db($db_name);

 

$param_userid = $_POST["userid"];

 

$text = $param_userid;

 

$query = "REPLACE INTO userlinks (userid,linkid) VALUES ((SELECT '$param_userid', linkid FROM links))";

$result = mysql_query($query);

 

if ( !mysql_query($query, $mysql_connection) ){

 

    die('ERROR: '. mysql_error() );

 

}

 

echo "RECORDS UPDATED";

 

?>

 

But again that produces that silly "ERROR: Column count doesn't match value count at row 1" error

[jimmyoneshot]

Whoops I forgot the code tags. Sorry. Can't edit it now 

[Ken2k7]

Try this SQL:

SELECT ul.userid, ul.linkid, l.categoryid, l.label, l.icon, l.url

FROM userlinks ul

INNER JOIN links l

    ON (ul.linkid = l.linkid)

WHERE ul.favourited = 'N';

 

Here are the results:

mysql> SELECT ul.userid, ul.linkid, l.categoryid, l.icon, l.url
    -> FROM userlinks ul
    -> INNER JOIN links l
    ->      ON (ul.linkid = l.linkid)
    -> WHERE ul.favourited = 'N';
+--------+--------+------------+----------+-------------------------+
| userid | linkid | categoryid | icon     | url                     |
+--------+--------+------------+----------+-------------------------+
|      1 |     62 |          1 | sky      | http://www.sky.com      |
|      1 |     63 |          1 | google   | http://www.google.co.uk |
|      1 |     65 |          1 | play     | http://www.play.com     |
|      2 |     64 |          2 | facebook | http://www.facebook.com |
|      2 |     65 |          1 | play     | http://www.play.com     |
+--------+--------+------------+----------+-------------------------+
5 rows in set (0.01 sec)

[jimmyoneshot]

Hi Ken. Thanks for that. Sorry I forgot to mention here. Andrew Gauger has already helped me solve that first query via pms with something very similar to your solution and you're right it works great. It's just that second update/insert query that I'm struggling with now. 

[Ken2k7]

The second query I'm having trouble with is an update/insert query. Basically each time a user of my system logs in their userid is sent to my php file ($param_userid) I then want this to insert all the linkids from the links table into the linkids of the userlinks table FOR that specific user id and ignore any linkids they may already have i.e. update them. Here's the query I have so far but it doesn't seem to work:-

$sql = "UPDATE userlinks SET userid, linkid SELECT '$param_userid', linkid FROM links WHERE userid = '$param_userid'";
$result = mysql_query($sql);
if (mysql_affected_rows($result) == 0) {
$sql = "INSERT INTO userlinks( userid, linkid ) SELECT '$param_userid', linkid FROM links";
$result = mysql_query($sql);
}

 

Edit by thorpe: And

tags too!

 

That was confusing. Could you elaborate on that and or provide an example?

[jimmyoneshot]

Hi Ken. Here's my links table which will hold all the links and their details that my client the administrator adds/removes/updates. Primary key is linkid):-

 

http://i223.photobucket.com/albums/dd147/jimmyoneshot/linkstable.jpg

 

Here's the userlinks table which I want to hold all the linkids from the links table for each user i.e. each user has one of each link. The favourite column is set to the default of 'N' so this just gets added in automatically as a new row is added. The user can favourite/unfavourite links via my application which changes the value of this column for the link in question (I've already got that working). There are no primary keys for the userlinks table:-

 

http://i223.photobucket.com/albums/dd147/jimmyoneshot/userlinkstable.jpg

 

So when a user creates a new accoun the query will be fired which should create a copy of all of the linkids from the links table with the new userid applied like the way the other 2 users with userids 1 and 2 each have a copy of every link. I want it however so that if the administrator adds in new links the next time the user logs on the query should add in the new links for that user and ignore/update the values that are there for that user. A replace into query seems best as that should insert them all if it's a new user and insert any new ones as well as simply update any new ones for that if they are an existing user. But getting this working seems easier said than done.

[andrewgauger]

Try taking the second set of () out of the line:

 

$query = "REPLACE INTO userlinks (userid,linkid) VALUES ((SELECT '$param_userid', linkid FROM links))";

 

I'm guessing it is trying to parse it into VALUES ((1, 2), (2,2) ...)

 

 

[jimmyoneshot]

$query = "REPLACE into userlinks (userid,  linkid) VALUES ('$param_userid', (SELECT linkid FROM links))";

 

I switched the query to this and this produces the "subquery returns more than 1 row" error  :'(

 

I'm starting to think this just isn't possible  :-\

[andrewgauger]

Life got in the way yesteday 

 

So I set this up:

 

use tests;
create table userlink (userid int, linkid int, favorited char);
create table link (linkid int auto_increment primary key, categoryid int, label varchar(255), icon varchar(255), url varchar(255));

 

And populated link with:

insert into link (categoryid, label, icon, url) values (1, "sky", "sky", "http://www.sky.com/"), (1, "Google", "google", "http://www.google.co.uk/"), (3, "Facebook", "facebook", "http://www.facebook.com/")

 

Then I:

ALTER TABLE userlink ADD UNIQUE (userid, linkid)

 

So when I executed this query:

REPLACE INTO userlink (userid, linkid) SELECT '1' , linkid FROM link

 

I got 3 rows affected.

 

Lesson learned:

 

Insert sub queries do not use VALUES or parentheses. 

 

So I set a favorited value, and watched it get killed by this query, not to fret, my friend, try this:

 

REPLACE INTO userlink (userid, linkid, favorited) SELECT '$param_userid' , link.linkid, userlink.favorited FROM link join userlink on (userlink.linkid=link.linkid AND userlink.userid='$param_userid')

 

So, I am sorry it took a day longer than I quoted you, but hey, at least I don't charge

 

______________________________________________________________________________

One more thing

 

I wanted to alert you to the behavior of replace.

 

When I executed the query it said "6 rows affected"  (because the link table was 3 rows)

REPLACE is equivalent to a DELETE and an INSERT.  If you need to do num_rows_affected be aware of this.

[jimmyoneshot]

Hi Andrew. Thanks a lot for that. I knew we'd get there. Ha. I've changed the code for the query and result to this:-

 

$query = REPLACE INTO userlinks (userid, linkid, favourited) SELECT '1', links.linkid, userlinks.favourited FROM links join userlinks on (userlinks.linkid=links.linkid AND userlinks.userid='1');

$result = mysql_query($query);

 

But that gives me a parse error on the line with the query"unexpected T_STRING".

 

It's nothing to do with the semicolon before the query being missing as I checked that so it must be something to do with the code but php doesn't really help you out much when it comes to finding what word/bit of code is incorrect 

[andrewgauger]

You need to surround the string with double quotes.

 

$query = "REPLACE INTO userlinks (userid, linkid, favourited) SELECT '1', links.linkid, userlinks.favourited FROM links join userlinks on (userlinks.linkid=links.linkid AND userlinks.userid='1')";

 

Also as revealed through a PM, new rows aren't being created (such as ones that don't already exist in userlinks), which should be fixed by using a LEFT join:

 

$query = "REPLACE INTO userlinks (userid, linkid, favourited) SELECT '1', links.linkid, userlinks.favourited FROM links LEFT JOIN userlinks ON (userlinks.linkid=links.linkid AND userlinks.userid='1')";

 

Hope that helps.