Home | Community | Message Board
You are not signed in.
Sign In New Account
Forum Index Search Posts Trusted Vendors Highlights Galleries FAQ User List Chat Store Random Growery

The Spore Depot
This site includes paid links. Please support our sponsors.

General Interest > Science and Technology
Threaded Mode Threaded  Previous topic Previous  View all topics Index  Next topic Next 

Welcome to the Shroomery Message Board! You are experiencing a small sample of what the site has to offer. Please login or register to post messages and view our exclusive members-only content. You'll gain access to additional forums, file attachments, board customizations, encrypted private messages, and much more!

Shop: Unfolding Nature Unfolding Nature: Being in the Implicate Order   Sporeworks.EU Spores for European Microscopy   Original Sensible Seeds Bulk Cannabis Seeds   North Spore Injection Grain Bag   Myyco.com Golden Teacher Liquid Culture For Sale

Jump to first unread post Pages: 1 | 2 |  [ show all ]
ATTN: PHP/MySQL Gurus
    #7354356 -

Hi folks. Here's the deal.. I had a fully functional online music library with which I could add, edit and delete artists and albums, rate albums, keep notes, search, etc - all based on user/admin levels of access.

When I first made it it was all code. I've finally had the time to come up with a design for it, coded it out in CSS, and have been putting the functionality back in.

For some reason my search page will not comply. The user can either browse via prefix, or perform a search for an artist/album. I've put echoes in the if statements to see if they were functioning, and they were. But when I try to call and variables within the if statements it doesn't work. The if statements can tell what type of search is being performed, but will not save the search information from the GET url (search.php?searchtype=artists&search=____ or search.php?prefix=A).

code that shouldn't be pertinent to this problem (as far as I know):
Code:

<?php session_start(); ?>
<?php
	include("connection.php");
	
	$url="search.php";

	if(!session_is_registered('member_id')){
		$login=false;
	} else {
		$login=true;
	}

	$notes=mysql_query("SELECT COUNT(note) AS notes FROM notes");
	$num_notes=mysql_fetch_array($notes);
	
	$links=mysql_query("SELECT title,url FROM links WHERE feature='1' ORDER BY title ASC");

	function display_date($thedate){
		$bigitems=split(" ",$thedate);
		$caldate=$bigitems[0];
		$items=split("-",$caldate);
		$output_date=$items[1].".".$items[2].".".$items[0];
		echo $output_date;
	}
	
	function write_msg(){
		$msg=$_GET['msg'];
		if($msg=="f"&&$login==false){
			print("<p>Incorrect login information.</p><p> </p>");
		}
	}


the important code:
Code:

	
	$prefix=$_GET['prefix'];
	$searchtype=$_GET['searchtype'];
	$search=$_GET['search'];
	if ($prefix!=""){
		$var=$prefix;
		$recordset=mysql_query("SELECT artists.artist,artists.artist_id,albums.album,albums.album_id
		     FROM artists,albums WHERE artists.artist LIKE '$prefix%' AND artists.artist_id=albums.artist_id 	
		     ORDER BY artists.artist,albums.album ASC");
		function trace(){print("prefix: $var ".$var." $prefix ".$prefix);}
	}
	if ($search!=""){
		if ($searchtype=="artists"){
			function trace(){print("artists");}
		}
		if ($searchtype=="albums"){
			function trace(){print("albums");}
		}
	}
?>


So for example, $var nor $prefix in the $prefix!="" if statement do not communicate to my trace function..

I was getting the problem, and now began rewriting the code, and this is what I've got so far. It's driving me absolutely bat tits and I suspect it's probably some trivial little thing..

Will be handing out thank you ratings for this..


--------------------
From dust you are made and to dust you shall return.

Edited by elbisivni (09/01/07 08:56 PM)

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: elbisivni]
    #7354661 -

The reason you can't access the variables from within the functions is that they aren't declared in the global scope. You could fix this by doing something like "global $prefix, var;" but more importantly you're using functions in a weird way; unless you're intentionally going for some type of polymorphism I wouldn't conditionally declare a function based on the type of data you're going to feed it. I'd do as follows, assuming you want to pass a string to the trace function. If you want to pass an arbitrary number of variables, put them in an array and pass that as the argument instead.

Code:
<?php

function trace($msg){
	echo $msg;
}

[...]

$prefix = $_GET['prefix'];
$search = $_GET['search'];
$searchtype = $_GET['searchtype'];

if ($prefix){
	[...]
	trace("var: $var prefix: $prefix");
}

if ($search){
	if ($searchtype == "artists"){
		trace("artists");
	}
	if ($searchtype == "albums"){
		trace("albums");
	}
}

?>

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: Ythan]
    #7354807 -

Code:
$recordset=mysql_query("SELECT artists.artist,artists.artist_id,albums.album,albums.album_id FROM artists,albums WHERE artists.artist LIKE '$prefix%' AND artists.artist_id=albums.artist_id ORDER BY artists.artist,albums.album ASC");


Also, this query is subject to SQL insertion attacks. You should escape $prefix.


--------------------
Just another spore in the wind.

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: Seuss]
    #7355071 -

A while back when I first learned this my teacher told me it was safe (he was a really shitty teacher though).. Could you explain why?
Code:
WHERE Name LIKE '\%AAA%' {escape '\'}

And that would be sufficient, right?

When users perform a search the search forms/links function as GET variables so users can see the search variables in the URL itself.

In response to Ythans suggestion, my variables weren't global in the previous version and it worked fine - I'm trying to globalize them now and it's still not working.  To my knowledge there is no logical explanation for the error that is occurring.. I've done this a dozen times and this has never happened :confused:

The only thing I know I'm doing different is placing the majority of the code above the html instead of within the head or the body..


--------------------
From dust you are made and to dust you shall return.

Edited by elbisivni (08/31/07 08:03 AM)

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: elbisivni]
    #7355434 -

> Could you explain why?

Lets say your query looks something like "select * from notes where name='$name'" and $name comes from a form field. Now, lets say that I enter the following into the form "'; drop table notes; '1". When mysql gets the query, it sees "select * from notes where name=''; drop table notes; '1'" which is bad. You must escape the $name variable before using it in the query to ensure that any bad characters the user enters are removed. There is a nice mysql_escape() function included to do this for you.


--------------------
Just another spore in the wind.

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: Seuss]
    #7355496 -

Right, I remember my another teacher saying something about this. mysql_escape(); will work when placed directly below the query?


--------------------
From dust you are made and to dust you shall return.

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: elbisivni]
    #7355771 -

You need to make them global in the function, eg:

Code:

function test(){
	global $testvar;
	echo "Testvar is $testvar";
}
$testvar = "This is a test!";
test();


But again, you're better off doing:

Code:

function test($testvar){
	echo "Testvar is $testvar";
}
test("This is a test!");


Trust me on this one, as your program grows you don't want a million different variables polluting the global namespace. It leads to bugs and security problems.

Use mysql_real_escape_string on *any* user-submitted variable which you include unmodified in a query. Eg:

Code:
$name = $_GET['name'];
$number = $_GET['number'];

$query = sprintf("SELECT userPass FROM userTable WHERE userName = '%s' AND userNumber = '%d'", mysql_real_escape_string($name), mysql_real_escape_string($number));


Or more simple but less elegant:

Code:
$name = $_GET['name'];
$number = $_GET['number'];

$name_q = mysql_real_escape_string($name);
$number_q = mysql_real_escape_string($number);

$query = "SELECT userPass FROM userTable WHERE userName = '$name_q' AND userNumber = '$number_q'";


This is important! Be sure you understand how mySQL injections work and double-check your code for vulnerabilities before making it public.

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: Ythan]
    #7355835 -

Code:

<?php session_start(); ?>
<?php
	require_once('connection.php');
	include('lib_common.php');
	
	$url="search.php?";

	$prefix=$_GET['prefix'];
	$searchtype=$_GET['searchtype'];
	$search=$_GET['search'];
	if ($prefix){
		$url="search.php?prefix=".$prefix;
//		$recordset=mysql_query("SELECT artists.artist,artists.artist_id,albums.album,albums.album_id 
	FROM artists,albums WHERE artists.artist LIKE '$prefix%' AND artists.artist_id=albums.artist_id 
	ORDER BY artists.artist,albums.album ASC", mysql_real_escape_string($prefix));
		function search_results(){print("Found # artists and # albums matching (prefix) $prefix");}
	}
	elseif ($search){
		$url="search.php?searchtype=".$searchtype."&search=".$search;
		if ($searchtype=="artists"){
			function search_results(){print("Found # artists and # albums matching (artist) -SEARCH-");}
		}
		elseif ($searchtype=="albums"){
			function search_results(){print("Found # artists and # albums matching (album) -SEARCH-");}
		}
		else {
			function search_results(){print("The search criteria you have entered is invalid.");}
		}
	}
	else {
		function trace(){print("The search criteria you have entered is invalid.");}
	}
?>

later in the body I call:
Code:
<p><?php search_results() ?></p>


I'm just focusing on the if ($prefix){ statement right now until I can get that to work.. I want the search parameters to pass into the statement where I can print the results. So I don't know if the 2 methods you mentioned would work. From what I've attempted, they don't.

I also realize now I was using mysql_real_escape_string for my login. I've gotten a bit rusty with this stuff, and was only really a beginner to begin with. I much appreciate your fellas help.

what the search urls may look like:
Code:

search.php?prefix=A
search.php?searchtype=artists&search=AnArtist
search.php?searchtype=albums&search=AnAlbum


--------------------
From dust you are made and to dust you shall return.

Edited by elbisivni (09/01/07 08:56 PM)

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: elbisivni]
    #7356187 -

Well I think maybe you're confused about what I'm saying since you're still using functions incorrectly. Forget about defining a search_results function, it looks like you don't need it anyway. Put the text you want in a string and just echo that later on.

Code:
if ($prefix){
	$results = "Found # artists and # albums matching (prefix) $prefix";
} else if ($search){
	$url="search.php?searchtype=".$searchtype."&search=".$search;
	if ($searchtype == "artists"){
		$results = "Found # artists and # albums matching (artist) -SEARCH-";
	} else if ($searchtype=="albums"){
		$results = "Found # artists and # albums matching (album) -SEARCH-";
	} else {
		$results = "The search criteria you have entered is invalid.";
	}
}
[...]
echo $results;


No offense but it looks like you may need to spend a little more time learning about PHP's basic control structures... if you're confused about how functions work you'll have a long, arduous and bug-filled road ahead of you. Good luck!

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: Ythan]
    #7356382 -

It was the functions fault. Thank you. I am a beginner, haven't had as much time as I would like to brush up on the language considering I've learned HTML, XHTML, DHTML, CSS, JavaScript, PHP, MySQL, XML, ActionScript and C++ in 3 years time, on top of all the other courses. Fucking meat grinder..but I graduate in a week, and then I can smoke my college degree stuffed with absolutely nothing, it'll get me really out there..

Now..the code below is successful in returning an accurate $albumnum:
Code:

$recordset=mysql_query("SELECT artists.artist,artists.artist_id,albums.album,albums.album_id 
	FROM artists,albums WHERE artists.artist LIKE '$prefix%' AND artists.artist_id=albums.artist_id 
	ORDER BY artists.artist,albums.album ASC",mysql_real_escape_string($prefix));
$albumnum=mysql_num_rows($recordset);


but it does not utilize the sprintf and the escape_string, like the code below which doesn't work:
Code:

$query=sprintf("SELECT artists.artist,artists.artist_id,albums.album,albums.album_id FROM artists,albums 
	WHERE artists.artist LIKE '$prefix%' AND artists.artist_id=albums.artist_id 
	ORDER BY artists.artist,albums.album ASC",mysql_real_escape_string($prefix));
$recordset=mysql_query($query);
$albumnum=mysql_num_rows($recordset);


and finally, when using COUNT, should I still be applying the escape string?
Code:

$artistnum=mysql_query("SELECT COUNT(artist) AS artistnum FROM artists WHERE artist LIKE '$prefix%'");
$artistnum=mysql_fetch_array($artistnum);


I don't intend to take excessive advantage of your knowledge and your help.


--------------------
From dust you are made and to dust you shall return.

Edited by elbisivni (09/01/07 08:56 PM)

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: Ythan]
    #7358435 -

Okay, perhaps you might verify the effectiveness of this code, and then that's it..
Code:

if ($prefix){
	$url="search.php?prefix=".$prefix;
	$prefix=mysql_real_escape_string($prefix);
//	^^^^^^^^

	$recordset=mysql_query("SELECT artists.artist,artists.artist_id,albums.album,albums.album_id 
	      FROM artists,albums WHERE artists.artist LIKE '$prefix%' AND artists.artist_id=albums.artist_id
	      ORDER BY artists.artist,albums.album ASC");
	$albumnum=mysql_num_rows($recordset);

	$artistnum=mysql_query("SELECT COUNT(artist) AS artistnum FROM artists WHERE artist LIKE '$prefix%'");
	$artistnum=mysql_fetch_array($artistnum);

	$search_results="Found $artistnum[artistnum] artists and $albumnum albums beginning with \"$prefix\".";
}

Thanks!


--------------------
From dust you are made and to dust you shall return.

Edited by elbisivni (09/01/07 08:55 PM)

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: elbisivni]
    #7358599 -

Sorry, I was writing a long reply to your last post but then my computer didn't come out of suspend and I lost it. I was going to try again today. Anyway the gist of it was that if mysql_real_escape_string doesn't work for you, you might have an older version of PHP and you can use mysql_escape_string or addslashes or the PECL mySQL module instead. But yeah if the above code works for you, it should absolutely protect against injection attacks. Just be sure to escape any user-submitted variable which is included unmodified in a query. Even if it's just a simple SELECT COUNT(*), they can end your query with a semicolon and then include their own mySQL statement to steal passwords, drop your database, or just about anything else.

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: Ythan]
    #7359694 -

If you ever need an admin at the shroomery just start a thread titled "NEED PHP HELP" and then put something like "can u h00k me up with free sporez" it will get ythans attention def.


--------------------
The DJ's took pills to stay awake and play for seven days.

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: elbisivni]
    #7359822 -

This thread is soooo wide. It's making my eyes bleed. :eek:


--------------------
Republican Values:

1) You can't get married to your spouse who is the same sex as you.
2) You can't have an abortion no matter how much you don't want a child.
3) You can't have a certain plant in your possession or you'll get locked up with a rapist and a murderer.

4) We need a smaller, less-intrusive government.

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: Diploid]
    #7359906 -

The <wbr> tag is automatically inserted every 32 characters to allow long lines to wrap, but I guess it doesn't work on <pre> formatted text.

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: elbisivni]
    #7359936 -

Code:
hmm                                                                                                                                                                                                                                                                                                                                                                       i guess not


:smirk:


--------------------
No, no, you're not thinking, you're just being logical. ~ Niels Bohr

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: Diploid]
    #7359938 -

Wider threads for broader minds :awesome:



Another question for Ythan or Seuss if either are still around.. Everything is running smoothly except I'm finding much difficulty in returning the number of artists found associated with the albums returned from an album search.

Code:

$artistnum=mysql_query("SELECT artists.artist_id,albums.artist_id,albums.album, COUNT(*) 
        AS artistnum FROM artists INNER JOIN albums ON artists.artist_id=albums.artist_id 
        WHERE albums.album LIKE '%$search%' GROUP BY albums.album,albums.artist_id");
$artistnum=mysql_fetch_array($artistnum);
$artistnum=$artistnum[artistnum];


Getting the number of albums returned for any kind of search is cake.  Getting the numbers of artists returned for a prefix or an artist search is also cake.  But this one has me dumbfounded, I've tried probably 30 variations on this code on have had no luck.  Currently, it always returns '1'.  Any ideas?


--------------------
From dust you are made and to dust you shall return.

Edited by elbisivni (09/01/07 08:54 PM)

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: elbisivni]
    #7360127 -

I don't think you can do it. COUNT() is an aggregate function which means it works on the results in a group. When you GROUP BY albums.album, albums.artist_id, there is only one item in each group since presumably the album / artist_id combos are unique. Thus, COUNT() correctly returns '1'. If you GROUP BY albums.artist_id you will place all the albums with the same artist_id in a group. Then no matter how many albums there are by the artist you will only get one record; it will have the correct count but only a single album name (the last one it came across). I would just do the query without the COUNT and then add the results in PHP. If you only want the total number of results, not the results per artist, you could use PHP's mysql_num_rows() function. Otherwise you will have to check the artist_id for each record and keep a running total.

There are ways around this by using multiple queries or subqueries but I think you'll get the best performance by doing it the way I said. It's frustrating when you come across something you can't do cleanly in mySQL, but you'd better get used to it I promise this won't be the last time. :wink:

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: Ythan]
    #7360290 -

I have two queries for each search. The first gathers all the data to be displayed and the # of albums found. The second is to gather the # of artists associated with those albums. This is only for prefix and artist searches however, where [I believe] it is more efficient to use COUNT for the latter query.

I must have misunderstood what I read about COUNT and GROUP BY, but no matter, I wrote a simple function to count the # of artists without the need for a second query.

The only problem is that after my while function, in which the number is tallied, the recordset for my first query is spent, and doesn't execute correctly when I call another mysql_fetch_array while function to display the actual results. If I rerun the query it works, but that's ghetto programming. Reset($row) doesn't work either.


--------------------
From dust you are made and to dust you shall return.

Extras: Filter Print Post Top
Re: ATTN: PHP/MySQL Gurus [Re: elbisivni]
    #7364098 -

Usually what I do in a case like that is read the mySQL results into an array, that way they're stored and you can use them for different purposes throughout your script.

Code:
$res = mysql_query("SELECT * FROM blah");
$results = array();
while ($row = mysql_fetch_array($res)){
	$results[] = $row;
}
foreach ($results AS $key => $row){
	echo nl2br(print_r($row, true));
}

Extras: Filter Print Post Top
Jump to top Pages: 1 | 2 |  [ show all ]

Shop: Unfolding Nature Unfolding Nature: Being in the Implicate Order   Sporeworks.EU Spores for European Microscopy   Original Sensible Seeds Bulk Cannabis Seeds   North Spore Injection Grain Bag   Myyco.com Golden Teacher Liquid Culture For Sale

General Interest > Science and Technology
Threaded Mode Threaded  Previous topic Previous  View all topics Index  Next topic Next 

Similar ThreadsPosterViewsRepliesLast post
* php easter egg Mycomancer 5,309 7 05/02/06 03:15 AM
by Le_Canard
* PHP, MySQL Programmer (job offer in craigslist chicago!) ZippoZM 591 0 07/11/06 02:20 PM
by ZippoZ
* php & mysql Cepheus 801 8 01/22/07 01:36 PM
by Cepheus
* hooking openoffice to mysql automanM 791 2 07/22/05 02:54 PM
by automan
* PHP people HELLA_TIGHT 736 8 06/02/07 11:20 AM
by Ythan
* Want to learn PHP: Book recommendations? Gr8fulJ420 982 6 03/09/06 09:39 AM
by Shdwstr
* PHP Problem st0nedphucker 1,317 7 03/07/07 03:38 AM
by Seuss
* PHP simple login elbisivni 1,398 4 06/04/07 04:40 AM
by Seuss

Extra information
You cannot start new topics / You cannot reply to topics
HTML is disabled / BBCode is enabled
Moderator: trendal, automan, Northerner
2,112 topic views. 0 members, 25 guests and 0 web crawlers are browsing this forum.
[ Show Images Only | Sort by Score | Print Topic ]
Search this thread:

Copyright 1997-2026 Mind Media. Some rights reserved.

Generated in 0.029 seconds spending 0.005 seconds on 16 queries.